Database query method and device

Abstract

The present application relates to the field of database technology and discloses a database query method and apparatus for providing a database query method. The method includes: first, obtaining a first query statement for querying the number of primary values. Then, converting the first query statement into a second query statement for querying the number of non-primary values, wherein the primary value and the non-primary value are different field values ​​for the same query field. Next, querying the number of the non-primary values ​​based on the second query statement. Finally, determining the number of primary values ​​based on the number of field values ​​in the query field and the number of the non-primary values.

Description

Technical Field

[0001] The embodiments of the present application relate to the field of database technology, and in particular to a database query method and device. Background Art

[0002] In recent years, with the continuous development of information technology, databases have become widely used as a medium for storing massive amounts of data. In real-world applications, most user operations involve data queries. When databases contain millions or even tens of millions of data items, a single query operation often requires tens of seconds or even longer, resulting in a poor user experience.

[0003] In a database, how to query data is a technical problem that needs to be solved. Summary of the Invention

[0004] The embodiments of the present application provide a database query method and device, which are used to propose a database query method.

[0005] In a first aspect, a database query method is provided. First, a first query statement is obtained for querying the number of primary values. Then, the first query statement is converted into a second query statement for querying the number of non-primary values, where the primary value and the non-primary value are different field values ​​for the same query field. Next, the number of non-primary values ​​is queried based on the second query statement. Finally, the number of primary values ​​is determined based on the number of field values ​​of the query field and the number of non-primary values. Specifically, the number of primary values ​​is obtained by subtracting the number of non-primary values ​​from the number of field values.

[0006] In this embodiment, when the number of primary values ​​is to be queried, the number of non-primary values ​​is converted to be queried, and the number of field values ​​of the query field and the number of non-primary values ​​are used to obtain the number of primary values. Therefore, a new database query method is proposed.

[0007] In one possible implementation, in the database, the ratio between the number of primary values ​​and the number of field values ​​of the query field to which the primary values ​​belong is greater than 50%. In this embodiment, the number of non-primary values ​​of a query field is defined as being less than the number of primary values. By converting the number of primary values ​​to the number of non-primary values, query time can be reduced, thereby improving data query efficiency.

[0008] In a possible implementation, the first query statement may be used to query the quantity of a primary value B1; when converting the first query statement into a second query statement for querying the quantity of a non-primary value, it may be to obtain That is, the second query statement is obtained.

[0009] Furthermore, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the number of primary values ​​may be determined according to: Determine the number of primary values, where the number of field values ​​of the query field is , that is, the number of field values ​​of the query field recorded in the database.

[0010] In a possible implementation, the first query statement may be used to query the quantity of two primary values ​​B1 and B2; when the first query statement is converted into a second query statement for querying the quantity of non-primary values, it may be obtained and That is, the second query statement is obtained.

[0011] Furthermore, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the number of primary values ​​may be determined according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0012] In a possible implementation, the first query statement is used to query the quantity of three primary values ​​B1, B2 and B3; when converting the first query statement into a second query statement for querying the quantity of non-primary values, it may be to obtain and That is, the second query statement is obtained.

[0013] Furthermore, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the number may be determined according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0014] In one possible implementation, before converting the first query statement into a second query statement for querying the number of non-primary values, it may be determined whether all query fields in the first query statement have indexes; and / or whether all query fields in the first query statement belong to precise queries. If all query fields have indexes and / or all belong to precise queries, the first query statement is subsequently converted into a second query statement for querying the number of non-primary values.

[0015] In a possible implementation, the first query statement may further include: a first condition, then the first query statement may be used to query the number of primary values ​​under the first condition. The second query statement may further include: the first condition, then the second query statement is used to query the number of non-primary values ​​under the first condition. Then, the number of non-primary values ​​under the first condition is queried according to the second query statement. Finally, the number of primary values ​​under the first condition may be determined based on the number of field values ​​of the query field under the first condition and the number of non-primary values ​​under the first condition, that is, the number of field values ​​under the first condition is subtracted from the number of non-primary values ​​under the first condition to obtain the number of primary values ​​under the first condition. The first condition here may be the non-primary value of a certain field.

[0016] In a possible implementation, if the first query statement queries the number of a primary value B1 under the first condition C, then when converting the first query statement into the second query statement, it may be to obtain

[0017] Furthermore, when determining the number of primary values ​​under the first condition based on the number of field values ​​of the query field under the first condition and the number of non-primary values ​​under the first condition, the number of primary values ​​under the first condition may be determined based on: Determine the number of primary values ​​under the first condition, wherein the number of field values ​​of the query field under the first condition is C (i.e. The number of

[0018] In a possible implementation, if the first query statement queries the quantity of two primary values ​​B1 and B2 under the first condition C, then when converting the first query statement into the second query statement, it may be to obtain as well as

[0019] Furthermore, when determining the number of primary values ​​under the first condition based on the number of field values ​​of the query field under the first condition and the number of non-primary values ​​under the first condition, the number of primary values ​​under the first condition may be determined based on:

[0020] Determine the number of primary values ​​under the first condition, wherein the number of field values ​​of the query field under the first condition is C (i.e. ) quantity.

[0021] In one possible implementation, if the field value corresponding to the query field includes multiple sub-field values, when at least one sub-field value among the multiple sub-field values ​​is a main value or a whole composed of multiple sub-field values ​​is a main value, then the field value corresponding to the query field is determined to be the main value.

[0022] In a second aspect, a database query device is provided, wherein the device implements the functions of the first aspect and any possible implementation of the first aspect. These functions can be implemented by hardware or by hardware executing corresponding software. The hardware or software includes one or more functional modules corresponding to the above functions.

[0023] In a third aspect, a computer program product is provided, comprising: a computer program code, which, when executed on a computer, enables the computer to execute the method executed in the first aspect and any possible implementation of the first aspect.

[0024] In a fourth aspect, the present application provides a database query device, which includes a processor and a memory, and the processor and the memory are electrically coupled; the memory is used to store computer program instructions; the processor is used to execute part or all of the computer program instructions in the memory, and when the part or all of the computer program instructions are executed, it is used to implement the functions of the above-mentioned first aspect and any possible implementation method of the first aspect. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 A schematic diagram of a data query process provided in an embodiment of the present application;

[0026] Figure 2 A structural diagram of a database query device provided in an embodiment of the present application;

[0027] Figure 3 This is a structural diagram of a database query device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0028] The embodiments of the present application will be described in detail below with reference to the accompanying drawings.

[0029] To facilitate understanding of the embodiments of the present application, some terms used in the embodiments of the present application are explained below to facilitate understanding by those skilled in the art.

[0030] 1) The "things" in a set are called elements. A set is a collection of one or more distinct elements. If x is an element of set A, then x∈A is written as x∈A. Elements in a set have three characteristics: determinism (the elements in a set must be distinct); heterogeneity (the elements in a set are distinct from one another; for example, if set A = {1, a}, then a cannot be equal to 1); and disorder (the elements in a set have no order; for example, the sets {3, 4, 5} and {3, 5, 4} are considered the same set).

[0031] 2) Add a horizontal line above the set letter to indicate the complement of the set letter, that is, all elements that do not belong to the set letter. For example, the set is the complement of set A1.

[0032] 3) Inclusion-exclusion principle: This means calculating the number of all objects contained in a certain content without considering the overlapping situation first, and then excluding the repeated counts, so that the result of the calculation has neither omissions nor duplications. This counting method is called the inclusion-exclusion principle.

[0033] Example 1: If the things being counted are of two categories, A and B, then the total number of elements in category A and category B = the number of elements in category A + the number of elements in category B - the number of elements in both category A and category B, that is, A∪B=A+BA∩B.

[0034] Example 2: If the things being counted are of three categories, A, B, and C, then the total number of elements in categories A, B, and C = the number of elements in category A + the number of elements in category B + the number of elements in category C - the number of elements that are both category A and category B - the number of elements that are both category A and category C - the number of elements that are both category B and category C + the number of elements that are both category A, category B, and category C, that is, A∪B∪C=A+B+CA∩BB∩CC∩A+A∩B∩C.

[0035] 4) An index is a structure that sorts the values ​​of one or more columns in a database table. Using an index allows you to quickly access specific information in a database table. For example, an index is recommended for the name column. If you want to find a specific employee by his or her last name, the index helps you get the information faster than searching all the rows in the table.

[0036] In this application, "and / or" describes the relationship between associated objects, indicating that three possible relationships exist. For example, "A and / or B" can represent: A exists alone, A and B exist simultaneously, and B exists alone. The character " / " generally indicates that the associated objects are in an "or" relationship.

[0037] The term "plurality" in this application refers to two or more.

[0038] In the description of this application, words such as "first" and "second" are only used for the purpose of distinguishing the description and cannot be understood as indicating or implying relative importance or order.

[0039] Additionally, in the embodiments of this application, the word "exemplary" is used to indicate an example, illustration, or description. Any embodiment or implementation described in this application as "exemplary" should not be construed as preferred or advantageous over other embodiments or implementations. Rather, the use of the word "exemplary" is intended to present concepts in a concrete way.

[0040] To facilitate understanding of the embodiments of the present application, the application scenarios of the present application are introduced below. The business scenarios described in the embodiments of the present application are intended to more clearly illustrate the technical solutions of the embodiments of the present application, and do not constitute a limitation on the technical solutions provided by the embodiments of the present application. Those skilled in the art will know that with the emergence of new business scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.

[0041] As shown in Table 1, a data table in a database is provided. For example, taking a MySQL relational database as an example, the data table is named table and includes m fields, namely, c i (i∈[1, m]), each row under a field records the field value corresponding to the field, and the number of rows recorded in Table 1 is the total number of all field values ​​corresponding to the field.

[0042]

[0043] Table 1

[0044] Field c1 can be, for example, "name", field c2 can be, for example, "age", field c3 can be, for example, "gender", field c4 can be, for example, "province", ... For the field "age", the corresponding field value recorded in the data table can be, for example, "18", "19", "20", etc. For the field "name", the corresponding field value recorded in the data table can be, for example, "Zhang San", "Li Si", etc. For the field "gender", the corresponding field value recorded in the data table can be, for example, "female", "male", etc. For the field "province", the corresponding field value recorded in the data table can be, for example, "Hebei Province", "Tianjin", "Beijing", "Henan Province", etc. In this application, the field value can also be referred to as the value of the field.

[0045] For example, 100 rows are recorded. Among these 100 rows, for "gender", 70 rows are "male" and 30 rows are "female".

[0046] For another example, among these 100 rows, for age, there are 30 rows with "18", 30 rows with "19", and 40 rows with "20".

[0047] For another example, among these 100 rows, for provinces, there are 20 rows with "Beijing" and 80 rows with "Hebei".

[0048] Among the m fields mentioned above, there may be k fields with indexes, where k is an integer less than or equal to m and greater than or equal to 0. These k indexed fields are cK1, cK2, ..., cK k For example, we can record I(cK)={cK1,cK2,...,cKk For example, field c1 "province", field c2 "age", and field c3 "gender" are all indexed, but field c4 "name" is not indexed.

[0049] Among the k indexed fields, there are s fields whose values ​​(i.e., field values) are enumerable (i.e., a finite number, such as h), where s is an integer less than or equal to k and greater than or equal to 0. These s fields are cS1, cS2, ..., cS S For example, we can record I(cS)={cS1,cS2,...,cS S For example, the values ​​of field c1 "province", field c2 "age", and field c3 "gender" are all enumerable.

[0050] The values ​​of these s fields belong to the value set V(cS l ), namely cS l Any value of (l∈[1,s]) belongs to V(cS l ).

[0051] For example, V(cS2)={cS2_value1,cS2_value2,...,cS2_value h};

[0052] V(cS l )={cS l _value1,cS l _value2,...,cS l _value h}, h is an integer greater than or equal to 0.

[0053] When a field value in a data table accounts for more than half of the total number of rows in the data table for all values ​​of that field, that field value is called a primary value. This also means that in the database, the ratio between the number of primary values ​​and the number of field values ​​in the query field to which the primary value belongs is greater than 50%.

[0054] For example, in the data table, there are 100 rows of records for the field "Gender". Among these 100 rows, the field value "Male" is recorded in 70 rows and the field value "Female" is recorded in 30 rows. The field value "Male" exceeds 50%, and can be regarded as the main value of the field "Gender".

[0055] For another example, among these 100 rows, for age, there are 30 rows with the field value "18", 30 rows with the field value "19", and 40 rows with the field value "20". This field "age" has no primary value.

[0056] For another example, among these 100 rows, for the province, there are 20 rows with the field value "Beijing" and 80 rows with the field value "Hebei". The field value "Hebei" is the main value of the field "Province".

[0057] Of course, when determining which field value is the primary value, 50% can be used as a reference standard, or any value between 50% and 100% can be used as a reference standard, such as 70%, 75%, 80%, 82%, etc. l In the , there exists a value cS l _value T (T∈[1,h]), which satisfies the 80 / 20 rule, i.e., the field cS l In the field value cS l _value T The number of rows in the data table occupies the field cS l The ratio of the total number of rows recorded in the data table is greater than or equal to 80%, that is, Then the field value cS l _value T Recorded as field cS l The main value of .

[0058] In this application, the field word of each field to be queried by the database statistical data query statement may be a primary value or may not be a primary value.

[0059] The following first introduces the case where the field values ​​of each field to be queried in the query statement are all primary values: For example, the database statistical data query statement is:

[0060] select count(*)from table where cS1 in A(cS1)

[0061] and cS2 in A(cS2)

[0062] ...and cS s in A(cS s ).

[0063] Where table is the name of a data table in the database. The query condition in this data query statement is: cS1 in A(cS1)and cS2in A(cS2)...and cS s in A(cS s ). Among them, cS1 inA(cS1), cS2in A(cS2), ..., cS s in A(cS s) can be regarded as a sub-query condition in the query condition. in means to perform an exact query on the value of the field. The field cS l The field value is A(cS l ), (l∈[1,s]), the field value may include the primary value cS l _value T In addition to the main value, the field value can also include non-main values, such as A(cS l )=(...,cS l _value T ,...).

[0064] For example, cS3 represents the field "province", A(cS3) represents the field value of the province to be queried, for example, to query "Hebei", then cS3_value T It can represent "Hebei". The field values ​​corresponding to the provinces recorded in the data table include "Beijing" and "Hebei", among which "Hebei" is the main value of the province.

[0065] For another example, cS2 represents the field "gender", and A(cS2) represents the field value of the gender to be queried. For example, if you query "male", then cS2_value T It can represent "male". The field values ​​corresponding to the gender recorded in the data table include "female" and "male", among which "male" is the main value of age.

[0066] The above query condition refers to the field cS to be queried l (l∈[1,s]) that meet the conditions set A(cS l ) can also be understood as the total number of data that meets the condition set A(cS l ) number of lines.

[0067] For example, for the query condition: cS1 in A(cS1)and cS2 in A(cS2)...and cS s in A(cS s ), query the set A1 according to cS1 in A(cS1), query the set A2 according to cS2 in A(cS2), ..., query the set A1 according to cS s in A(cS s )Query the set As.

[0068] For the sets A1, A2, ..., As, the following transformations can be performed according to the principle of inclusion and exclusion:

[0069]

[0070] in, Represents set A iThe complement of , ∩ represents the intersection, (-1) (s) represents -1 raised to the power of s, and Σ represents summation.

[0071] For example, when s=1,

[0072] For another example, when s=2,

[0073] For another example, when s=3,

[0074] As mentioned above, a field's primary value is defined as the number of rows in the data table that record that primary value exceeding half of the total number of rows in the data table that record that field. Querying based on the primary value of this field requires more content to be queried, resulting in lower data query efficiency. Based on this, referring to Table 2, we can reason based on the aforementioned transformation of query results A1, A2, ..., As using the inclusion-exclusion principle to transform the query conditions in the query statement. Specifically, when querying the primary value, we determine the number of primary values ​​to be queried by querying the number of non-primary values ​​(i.e., the complement of the primary value) and combining this with the number of field values, thereby improving query efficiency.

[0075] For example, a data table contains 100 rows for the "Gender" field. Of these 100 rows, 70 contain the "Male" field value, and 30 contain the "Female" field value. The "Male" field value is the primary value for "Gender." To query the number of males, first query the number of females. Then, subtract the number of females (30) from the "Gender" field value (100), resulting in a total of 70 males. This query only requires querying 30 rows for "Female," which is more efficient than querying the 70 rows corresponding to "Male."

[0076] Table 2 shows the correspondence between the query conditions of database statistical data and the conditions of the inclusion-exclusion principle.

[0077]

[0078]

[0079] Table 2 Based on the inclusion-exclusion principle transformation of Table 2 above, the above query conditions can also be transformed as follows:

[0080]

[0081] in, V(cS s ) is the field cS s In addition, it should be noted that the query conditions before the equal sign include fields. For the convenience of description, the query conditions after the equal sign will filter out the fields. For example, A(cSs ) is actually the field cS s The field value in .

[0082] In the database query, since the field cS s The condition set A(cS s ) contains the main value cS l _value T , the main value can be, for example, a field value that complies with the 80 / 20 rule. The database statistical query performance of the combined condition is better than A(cS s )Combined condition query, that is Combined condition query time Less than A(cS s )Combined condition query T(A(cS s )).

[0083] For example, by Combined condition query time The maximum value of Then when When the combined condition query is executed in parallel tasks (ignoring other system time overhead), the efficiency improvement η1 is

[0084] For example, the following Table 3 records the test results of performance tests on data tables with different numbers of record rows when s=2, in units of / s.

[0085]

[0086]

[0087] Table 3

[0088] From Table 3, we can see that A(cS s )Combined condition query time T(A(cS s )) is the time corresponding to A1∩A2. Combined condition query time Including: 1=1 corresponding time, The corresponding time, The corresponding time, The corresponding time. If the conditions in the combination are parallel queries, only the maximum value is considered. For example, when the data volume is 1 million, if according to A(cS s ) condition to query the time T(A(cS s )) is 10.501s. If the transformed Combining query conditions to perform a query takes a maximum of 2.025 seconds, improving query efficiency by 81%. Generally, the more data recorded, the greater the improvement in efficiency.

[0089] Combined with the above introduction, see Figure 1 This example introduces a database query process, which includes the following steps:

[0090] Step 101: Obtain a first query statement for querying the quantity of a primary value.

[0091] Typically, in a database, the ratio between the number of the primary values ​​and the number of field values ​​of the query field to which the primary values ​​belong is greater than 50%.

[0092] The format of the query statement has been introduced above, which is for querying a data table. The first query statement here can also be for querying a data table in the database. Optionally, for the convenience of description, the following database can represent a data table in the database.

[0093] The first query statement can be used to query the number of one or more primary values. A(cS2)A(cS2) has been used above to describe the primary value. For ease of understanding, B will be used to replace A(cS2). B1 can be regarded as A(cS1), B2 can be regarded as A(cS2), and so on.

[0094] Step 102: Convert the first query statement into a second query statement for querying the number of non-primary values, where the primary value and the non-primary value are different field values ​​for the same query field.

[0095] Before converting the first query statement into a second query statement for querying the number of non-primary values, the following steps 102a and 102b may be further performed.

[0096] Step 102a: Determine whether all query fields in the first query statement have indexes. If they all have indexes, the process can continue; if not, data query can be performed according to the first query statement in step 101.

[0097] Step 102b: Determine whether all query fields in the first query statement belong to precise query. If they all belong to fine query, the process can continue. If they do not belong to fine query (i.e. fuzzy query), data query can be performed according to the first query statement in step 101.

[0098] The above steps 102a and 102b are optional steps, and the order of the steps is not limited.

[0099] Through step 102a, the fields with indexes can be queried, thereby improving query efficiency.

[0100] Through step 102b, a detailed query can be performed on the field to improve the query accuracy.

[0101] Step 103: Query the number of the non-primary values ​​according to the second query statement.

[0102] Step 104: Determine the number of primary values ​​based on the number of field values ​​of the query field and the number of non-primary values. For example, the number of primary values ​​is determined by subtracting the number of non-primary values ​​from the number of field values ​​of the query field.

[0103] The number of field values ​​of the query field here (i.e., the number of rows recorded in the database) can be already saved in the database and does not need to be queried through a query statement. In this case, the time required to obtain the number of field values ​​can be considered as 0. For example, in the example of Table 3, the time corresponding to 1=1 is infinitely close to 0. When the amount of data recorded is 1 million, if according to A(cS s ) condition to query the time T(A(cS s )) is 10.501s. If the transformed Combining query conditions to perform a query takes a maximum of 0.588 seconds, improving query efficiency by 99%.

[0104] Of course, the number of field values ​​of the query field here can also be found out through a query statement. For example, the second query statement can include all field values ​​of the query field, and the second query statement can also query the number of field values ​​of the query field; or the number of field values ​​of the query field can be queried through other query statements.

[0105] In one example, the first query statement may be used to query the quantity of a primary value B1; when the first query statement is converted into a second query statement for querying the quantity of a non-primary value, it may be obtained That is, the second query statement is obtained.

[0106] Furthermore, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the number of primary values ​​may be determined according to: Determine the number of primary values, where the number of field values ​​of the query field is , that is, the number of field values ​​of the query field recorded in the database.

[0107] For example, there are 100 rows in the database;

[0108] For gender, there are 70 rows with the field value "Male" and 30 rows with the field value "Female". Therefore, the field value "Male" is the primary value of the field "Gender".

[0109] For age, there are 30 rows with the field value "18", 30 rows with the field value "19", 40 rows with the field value "20", and no primary value for the field "age".

[0110] For the province, there are 20 rows with the field value "Beijing" and 80 rows with the field value "Hebei". The field value "Hebei" is the primary value of the field "Province".

[0111] For example, the first query statement is to query the number of "male" (B1). Since "male" is the primary value, the second query statement can query the number of "female". Then take the number of gender field values ​​100, subtract the number of "female" 30, and you can get the number of "male" 70.

[0112] In one example, the first query statement may be used to query the quantity of two primary values ​​B1 and B2; when the first query statement is converted into a second query statement for querying the quantity of non-primary values, it may be obtained and That is, the second query statement is obtained.

[0113] Furthermore, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the number of primary values ​​may be determined according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0114] Combined with the above example, for example, the first query statement is to query the number of "males in Hebei". Since "male" is the primary value and "Hebei" is the primary value, the second query statement can query the number of "female", "Beijing", and "female in Beijing". Then take the number of gender field values ​​100, subtract "female" The number is 30, minus "Beijing" The number of 20, minus "Beijing women" The number of “males in Hebei” can be obtained by adding the number of

[0115] In one example, the first query statement is used to query the quantity of three primary values ​​B1, B2, and B3; when the first query statement is converted into a second query statement for querying the quantity of non-primary values, it can be obtained and That is, the second query statement is obtained.

[0116] Furthermore, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the number may be determined according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0117] If the first query statement is to query the number of 4, 5, or even more primary values, the above principle can be used for analogy, and examples will not be repeated here.

[0118] In addition, it should be noted that if the field value corresponding to the query field includes multiple sub-field values, when at least one sub-field value among the multiple sub-field values ​​is a main value or a whole composed of multiple sub-field values ​​is a main value, then the field value corresponding to the query field is determined to be the main value.

[0119] For example, when querying the field "age," the field value to be queried includes the subfield values ​​"17" and "18." "17" and "18" can be considered as a whole, and these two subfield values ​​are considered the main value of the field "age."

[0120] Optionally, when the first query statement is obtained in the present application, a preliminary analysis may be performed to determine whether the first query statement includes a primary value, as well as which field values ​​are primary values ​​and which field values ​​are not primary values. For example, the primary value and non-primary value of the field values ​​in the first query statement may be determined based on a predetermined statistical report. Alternatively, the primary value and non-primary value of the field values ​​in the first query statement may be determined based on the stored field values ​​of the primary value. The stored field values ​​of the primary value may be those that were previously queried based on a previous query statement. In other words, the field values ​​have been previously queried.

[0121] In another embodiment of the present application, the first query statement may further include: a first condition, then the first query statement may be used to query the number of primary values ​​under the first condition. The second query statement may further include: the first condition, then the second query statement is used to query the number of non-primary values ​​under the first condition. Then, the number of non-primary values ​​under the first condition is queried according to the second query statement. Finally, the number of primary values ​​under the first condition can be determined based on the number of field values ​​of the query field under the first condition and the number of non-primary values ​​under the first condition, that is, the number of field values ​​under the first condition is subtracted from the number of non-primary values ​​under the first condition to obtain the number of primary values ​​under the first condition. The first condition here can be the non-primary value of a certain field.

[0122] In one example, if the first query statement queries the number of a primary value B1 under the first condition C, then when converting the first query statement into the second query statement, it may be to obtain

[0123] Furthermore, when determining the number of primary values ​​under the first condition based on the number of field values ​​of the query field under the first condition and the number of non-primary values ​​under the first condition, the number of primary values ​​under the first condition may be determined based on: Determine the number of primary values ​​under the first condition, wherein the number of field values ​​of the query field under the first condition is the number of C. Determine the number of primary values ​​under the first condition, wherein the number of field values ​​of the query field under the first condition is The number of

[0124] For example, the first query statement is to query the number of 17-year-old "male", where 17 years old is the first condition C. Since "male" (B1) is the primary value, the second query statement can query the number of 17-year-old "female". Then take the number of all people aged 17 (C) and subtract the number of “female” aged 17 , we can get the number of “males” aged 17.

[0125] In one example, if the first query statement queries the quantity of two primary values ​​B1 and B2 under the first condition C, then when converting the first query statement into the second query statement, it may be to obtain as well as

[0126] Furthermore, when determining the number of primary values ​​under the first condition based on the number of field values ​​of the query field under the first condition and the number of non-primary values ​​under the first condition, the number of primary values ​​under the first condition may be determined based on:

[0127] Determine the number of primary values ​​under the first condition, wherein the number of field values ​​of the query field under the first condition is the number C. That is, according to:

[0128] Determine the number of primary values ​​under the first condition, wherein the number of field values ​​of the query field under the first condition is The number of

[0129] For another example, the first query statement is to query the number of 17-year-old "males in Hebei", where 17 years old is the first condition. Since "male" (B1) is the primary value and "Hebei" (B2) is the primary value, the second query statement can query the number of 17-year-old "females". The number of 17-year-old "Beijing" The number of "Beijing women" aged 17 Then take the number of all people aged 17 (C) and subtract the number of “female” aged 17 minus the 17-year-old "Beijing" minus the 17-year-old "Beijing women" , we can get the number of “males in Hebei” aged 17.

[0130] Next, combine the above examples and the previously introduced Combine conditions and explain the query statement containing the first condition in detail:

[0131] The first query statement is as follows: the field values ​​of some fields include primary values, while the field values ​​of other fields are all non-primary values:

[0132] select count(*)from table where cS1 in A(cS1)

[0133] and cS2 in A(cS2)...and cS s inA(cS s )

[0134] and cond(I(cK)-I(cS))

[0135] As mentioned above: cS1 inA(cS1), cS2 in A(cS2), ..., cS s in A(cS s ) can be regarded as a sub-query condition in the query condition. Among them, in means to perform an accurate query on the value of the field, and the field cS l The field value is A(cS l ), (l∈[1,s]), the field value may include the primary value cS l _value T In addition to the primary value, the query field value can also include non-primary values. For example, A(cS l )=(...,cS l _value T ,...).

[0136] In addition, it should be introduced that cond(I(cK)-I(cS)) can be understood as the field to be queried in the query statement is I(cK)={cK1,cK2,...,cK k ], where the field containing the primary value is I(cS) = {cS1, cS2, ..., cS S], cond(I(cK)-I(cS)) can represent the set of field values ​​to be queried corresponding to the remaining fields except the field containing the main value in the field to be queried.

[0137] For example, the query statement is to find "17-year-old male junior high school students in Hebei." The field value "Male" is the primary value of the field "Gender," the field value "Hebei" is the primary value of the field "Province," the field value "17" is not the primary value of the field "Age," and the field value "Junior High School Student" is not the primary value of the field "Education." Therefore, I(cK) = {age, gender, province, education], I(cS) = {gender, province], and cond(I(cK) - I(cS)) includes the field value "17" for the field "Age" and the field value "Junior High School Student" for the field "Education."

[0138] Based on the inclusion-exclusion principle introduced above, the query conditions in this query statement can be transformed as follows:

[0139]

[0140] In this transformation, since the field values ​​included in cond(I(cK)-I(cS)) are not primary values, they can remain unchanged and there is no need to use the complement method for query.

[0141] Since the field cS s The field value set A(cS s ) contains the main value cS l _value T ,so The performance of the database statistical data query statement with the combination of cond(I(cK)-I(cS)) is better than that of A(cS s ) and cond(I(cK)-I(cS)) combine query condition performance. Combined query time with cond(I(cK)-I(cS)): cond(I(cK)-I(cS))), less than A(cS s ) and cond(I(cK)-I(cS)) combination condition query T(cond(A(cS s )),cond(I(cK)-I(cS))).

[0142] For example, by The maximum query time of the combination of cond(I(cK)-I(cS)) is Then when When the query is combined with cond(I(cK)-I(cS)) and executed in parallel (ignoring other system time overhead), the efficiency η2 is improved as follows:

[0143]

[0144] The following example uses a practical construction to illustrate the data query process and the efficiency improvement.

[0145] The data table is named test and has three fields: id, enumParam1, and enumParam2. Indexes are created for these three fields: id, enumParam1, and enumParam2.

[0146] For example, in the data table test there are records: That is, the field values ​​of the field "enumParam1" in the data table test include "online" and "offline", and the field values ​​of the field "enumParam2" include "success", "failed" and "running".

[0147] For the field "enumParam1," the field value of integer row records with an integer value of 10 is "offline," for example, the field value of the records in row 10, row 20, row 30, and so on is "offline." The field value of integer row records other than 10 is "online," for example, the field value of the records in rows 1 to 9, rows 11 to 19, rows 21 to 29, and so on is "online."

[0148] The data structured in this example is distributed as follows (id is an increasing integer and can represent the row number). mod represents the remainder, id mod10=0, which means the remainder after division by 10 is 0, i.e., the number of rows is an integer of 10.

[0149]

[0150] For the "enumParam2" field, the field value for rows with an integer value of 10 or a non-integer value of 19 is "failed." For example, the field value for rows 10 and 20, rows 1 through 9, and rows 11 through 18 is "failed." The field value for rows with an integer value of 19 is "running." For example, row 190 is an integer value of 19 and also an integer value of 10, so the field value can be "running." The field value for rows with an integer value of 10 or a non-integer value of 10 is "success."

[0151] The data structured in this example is distributed as follows (id is an increasing integer and can represent the row number). mod represents the remainder, and id mod 10 = 0, which means the number of rows is an integer of 10.

[0152]

[0153] For example, the query conditions can be:

[0154] The query condition includes two (i.e., m=2) sub-query conditions, namely "enumParam1in("online")" and "enumParam2in("success","failed")". The sub-query condition enumParam1 in("online") includes the query field "enumParam1" and the field value is "online". The sub-query condition "enumParam2in("success","failed")" includes the query field "enumParam2" and the field values ​​include "success" and "failed".

[0155] From this, it can be concluded that

[0156] Then the value exists: enumParam1_value T = "online", and enumParam2_value T = "success", which satisfies the 80 / 20 principle. In the above query conditions, the field value "online" to be queried in the query field "enumParam 1" and the query field "enumParam2" is both the primary value.

[0157] For example, according to the inclusion-exclusion principle introduced above:

[0158] as well as

[0159] as well as,

[0160] It can be concluded that:

[0161] select count(*)from test where enumParam1 in("online")

[0162] and enumParam2 in("success","failed"), the corresponding database statistics query statement is as follows:

[0163] A1∩A2→sum(enumParam1 in("online")and enumParam2in("success","failed"))

[0164] →select count(*)from test where enumParam1 in("online")

[0165] and enumParam2 in("success","failed")

[0166]

[0167]

[0168]

[0169]

[0170] In addition, it should be noted that if the field value corresponding to the query field includes multiple sub-field values, when at least one sub-field value among the multiple sub-field values ​​is a main value or a whole composed of multiple sub-field values ​​is a main value, then the field value corresponding to the query field is determined to be the main value.

[0171] For example, for the query field "enumParam2", the subfield values ​​include "success" and "failed". "Success" and "failed" can be considered as a whole, and these two subfield values ​​are considered as the main value of the field.

[0172] The improved efficiency can be seen in Table 3 and will not be repeated here. In summary, for database statistical query statements that meet the main value conditions of this application, the query optimization method based on the inclusion-exclusion principle can effectively improve the database query performance, especially for data statistical queries with large amounts of data, the system performance can be significantly improved.

[0173] The preceding text describes the database query method of the embodiments of the present application. The following text describes the database query device of the embodiments of the present application. The method and device are based on the same technical concept. Since the method and device solve similar problems, the implementation of the device and method can refer to each other, and any repetitions will not be repeated.

[0174] Based on the same technical concept as the query method of the above database, Figure 2 As shown, a database query device 200 is provided, which can perform the above Figure 1The apparatus 200 may be an electronic device or a chip used in an electronic device. The apparatus 200 may include:

[0175] An acquisition module 201 is configured to acquire a first query statement for querying the number of primary values;

[0176] A conversion module 202 is configured to convert the first query statement into a second query statement for querying the number of non-primary values, where the primary value and the non-primary value are different field values ​​for the same query field;

[0177] A query module 203 is configured to query the number of the non-primary values ​​according to the second query statement;

[0178] The determination module 204 is configured to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values.

[0179] In one example, the first query statement is used to query the quantity of a primary value B1; when the conversion module 202 is used to convert the first query statement into a second query statement for querying the quantity of a non-primary value, it is specifically used to: obtain

[0180] In an example, the determining module 204, when used to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values, is specifically used to:

[0181] according to: Determine the number of primary values, where the number of field values ​​of the query field is The number of

[0182] In one example, the first query statement is used to query the quantity of two primary values ​​B1 and B2; when the conversion module 202 is used to convert the first query statement into a second query statement for querying the quantity of non-primary values, it is specifically used to: obtain and

[0183] In an example, the determining module 204, when used to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values, is specifically used to:

[0184] according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0185] In one example, the first query statement is used to query the quantity of three primary values ​​B1, B2, and B3; when the conversion module 202 is used to convert the first query statement into a second query statement for querying the quantity of non-primary values, it is specifically used to: obtain and

[0186] In an example, the determining module 204, when used to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values, is specifically used to:

[0187] according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0188] In an example, the conversion module 202 is further configured to determine that all query fields in the first query statement have indexes; and / or determine that all query fields in the first query statement belong to precise queries.

[0189] Figure 3 It is a schematic block diagram of the query device 300 of the embodiment of the present application. It should be understood that the device 300 can perform the above Figure 1 The device 300 includes: a processor 310, and optionally, a communication interface 320 and a memory 330. The communication interface can be used to receive program instructions and transmit them to the processor, or the transceiver can be used for the device to communicate and interact with other communication devices, such as interactive control signaling and / or business data. The communication interface can be a code and / or data reading and writing transceiver, or the communication interface can be a signal transmission transceiver between the communication processor and the transceiver. The communication interface can also be replaced by a transceiver, an interface circuit, etc. The processor 310 and the memory 330 are electrically coupled.

[0190] For example, the memory 330 is used to store computer programs; the processor 310 can be used to call the computer program or instructions stored in the memory to execute the above-mentioned database query method.

[0191] In one example, the processor 310 is used to obtain a first query statement for querying the number of primary values; convert the first query statement into a second query statement for querying the number of non-primary values, where the primary value and the non-primary value are different field values ​​for the same query field; query the number of non-primary values ​​according to the second query statement; and determine the number of primary values ​​based on the number of field values ​​of the query field and the number of non-primary values.

[0192] In one example, the first query statement is used to query the quantity of a primary value B1; when the processor 310 converts the first query statement into a second query statement for querying the quantity of a non-primary value, it is specifically used to: obtain

[0193] In one example, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the processor 310 is specifically configured to: Determine the number of primary values, where the number of field values ​​of the query field is The number of

[0194] In one example, the first query statement is used to query the quantity of two primary values ​​B1 and B2; when the processor 310 converts the first query statement into a second query statement for querying the quantity of non-primary values, it is specifically used to: obtain and

[0195] In one example, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the processor 310 is specifically configured to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0196] In one example, the first query statement is used to query the quantity of three primary values ​​B1, B2, and B3; when the processor 310 converts the first query statement into a second query statement for querying the quantity of non-primary values, it is specifically used to: obtain and

[0197] In an example, when determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values, the processor 310 is specifically configured to:

[0198] according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of

[0199] In one example, before converting the first query statement into a second query statement for querying the number of non-primary values, the processor 310 is further used to determine that all query fields in the first query statement have indexes; and / or determine that all query fields in the first query statement belong to precise queries.

[0200] The above-mentioned processor may be a central processing unit (CPU), a network processor (NP), or a combination of a CPU and an NP. The processor may further include a hardware chip or other general-purpose processor. The above-mentioned hardware chip may be an application-specific integrated circuit (ASIC), a programmable logic device (PLD), or a combination thereof. The above-mentioned PLD may be a complex programmable logic device (CPLD), a field-programmable gate array (FPGA), a generic array logic (GAL), and other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc., or any combination thereof. The general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0201] It should also be understood that the memory mentioned in the embodiments of the present application may be a volatile memory or a non-volatile memory, or may include both volatile and non-volatile memories. Among them, the non-volatile memory may be a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), or a flash memory. The volatile memory may be a random access memory (RAM), which is used as an external cache. By way of example and not limitation, many forms of RAM are available, such as static random access memory (SRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate synchronous dynamic random access memory (DDR SDRAM), enhanced synchronous dynamic random access memory (ESDRAM), synchronous link dynamic random access memory (SLDRAM), and direct RAM bus random access memory (DR RAM). It should be noted that the memory described herein is intended to include, but is not limited to, these and any other suitable types of memory.

[0202] The transceiver, interface circuit, or transceiver described in the embodiments of the present application may include a separate transmitter and / or a separate receiver, or may be an integrated transmitter and receiver. The transceiver, interface circuit, or transceiver may operate under the instructions of a corresponding processor. Optionally, the transmitter may correspond to a transmitter in a physical device, and the receiver may correspond to a receiver in a physical device.

[0203] An embodiment of the present application further provides a computer storage medium storing a computer program. When the computer program is executed by a computer, the computer can be used to execute the above-mentioned database query method.

[0204] The embodiment of the present application also provides a computer program product containing instructions, which, when executed on a computer, enables the computer to execute the database query method provided above.

[0205] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0206] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0207] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0208] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0209] Although the preferred embodiments of the present application have been described, those skilled in the art may make additional changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the present application.

[0210] Obviously, those skilled in the art may make various changes and modifications to the embodiments of the present application without departing from the spirit and scope of the embodiments of the present application. Thus, if these modifications and variations of the embodiments of the present application fall within the scope of the claims of the present application and their equivalents, the present application is intended to include such modifications and variations.

Claims

1. A database query method, characterized in that: The method comprises: Obtain a first query statement for querying the number of primary values; Converting the first query statement into a second query statement for querying the number of non-primary values, where the primary value and the non-primary value are different field values ​​for the same query field; querying the number of the non-primary values ​​according to the second query statement; The number of the primary values ​​is determined according to the number of field values ​​of the query field and the number of the non-primary values.

2. The method according to claim 1, wherein In the database, a ratio between the number of the primary values ​​and the number of field values ​​of the query field to which the primary values ​​belong is greater than 50%.

3. The method according to claim 1 or 2, wherein: The first query statement is used to query the quantity of a primary value B1; and converting the first query statement into a second query statement for querying the quantity of a non-primary value includes: Get 4. The method according to claim 3, wherein The determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values ​​includes: according to: Determine the number of primary values, where the number of field values ​​of the query field is The number of 5. The method according to claim 1 or 2, wherein: The first query statement is used to query the quantity of two primary values ​​B1 and B2; and converting the first query statement into a second query statement for querying the quantity of non-primary values ​​includes: Get and 6. The method according to claim 5, wherein The determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values ​​includes: according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of 7. The method according to claim 1 or 2, wherein: The first query statement is used to query the quantity of three primary values ​​B1, B2, and B3; and converting the first query statement into a second query statement for querying the quantity of non-primary values ​​includes: Get and 8. The method according to claim 7, wherein The determining the number of primary values ​​according to the number of field values ​​of the query field and the number of non-primary values ​​includes: according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of 9. The method according to claim 1 or 2 or 4 or 6 or 8, wherein: Before converting the first query statement into a second query statement for querying the number of non-primary values, the method further includes: Determining that all query fields in the first query statement have indexes; and / or It is determined that all query fields in the first query statement belong to precise queries.

10. A database query device, characterized in that: The device comprises: An acquisition module, configured to acquire a first query statement for querying the number of primary values; A conversion module, configured to convert the first query statement into a second query statement for querying the number of non-primary values, where the primary value and the non-primary value are different field values ​​for the same query field; A query module, configured to query the number of the non-primary values ​​according to the second query statement; A determination module is used to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values.

11. The device according to claim 10, wherein In the database, a ratio between the number of the primary values ​​and the number of field values ​​of the query field to which the primary values ​​belong is greater than 50%.

12. The device according to claim 10 or 11, characterized in that The first query statement is used to query the number of a primary value B1; when the conversion module is used to convert the first query statement into a second query statement for querying the number of non-primary values, it is specifically used to: obtain 13. The device according to claim 12, wherein The determining module, when used to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values, is specifically used to: according to: Determine the number of primary values, where the number of field values ​​of the query field is The number of 14. The device according to claim 10 or 11, characterized in that The first query statement is used to query the quantity of two primary values ​​B1 and B2; when the conversion module is used to convert the first query statement into a second query statement for querying the quantity of non-primary values, it is specifically used to: obtain and 15. The device according to claim 14, wherein The determining module, when used to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values, is specifically used to: according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of 16. The device according to claim 10 or 11, characterized in that The first query statement is used to query the quantity of three primary values ​​B1, B2 and B3; when the conversion module is used to convert the first query statement into a second query statement for querying the quantity of non-primary values, it is specifically used to: obtain and 17. The device according to claim 16, wherein The determining module, when used to determine the number of the primary values ​​according to the number of field values ​​of the query field and the number of the non-primary values, is specifically used to: according to: Determine the number of primary values, where the number of field values ​​of the query field is and The number of 18. The device according to claim 10 or 11 or 13 or 15 or 17, characterized in that The conversion module is further configured to determine that all query fields in the first query statement have indexes; and / or determine that all query fields in the first query statement belong to precise queries.

19. A database query device, characterized in that: The device includes: a processor and a memory; The memory is used to store computer program instructions; The processor is configured to execute part or all of the computer program instructions in the memory, and when the part or all of the computer program instructions are executed, is configured to implement the method according to any one of claims 1 to 9.

20. A computer-readable storage medium, characterized in that A computer program is stored, and when the computer program is executed by a computer, the computer is caused to execute the method according to any one of claims 1 to 9.

Images