CN106874332B - Database access method and device - Google Patents
Database access method and device Download PDFInfo
- Publication number
- CN106874332B CN106874332B CN201610653819.7A CN201610653819A CN106874332B CN 106874332 B CN106874332 B CN 106874332B CN 201610653819 A CN201610653819 A CN 201610653819A CN 106874332 B CN106874332 B CN 106874332B
- Authority
- CN
- China
- Prior art keywords
- execution result
- predicate
- execution
- predicates
- updating
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24537—Query rewriting; Transformation of operators
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
Abstract
The application provides a database access method and device. The method comprises the following steps: determining a current execution order for predicates associated with the database access request; acquiring execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence; judging whether the predicate meets a sequential updating condition or not according to the execution result parameter; and when the predicates meet the sequence updating condition, updating the current execution sequence of the predicates based on the acquired execution result parameters, and continuously executing the process of acquiring the execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence. The predicate execution sequence can be dynamically adjusted according to the real total execution cost, and the change of data distribution is self-adapted, so that the predicate always keeps the minimum total execution cost in the whole execution process, the data access efficiency is improved, and the processing resources of the system are saved.
Description
Technical Field
The present application relates to the field of database technologies, and in particular, to a database access method and apparatus.
Background
SQL (Structured Query Language) predicates generally refer to conditional expressions in a where clause, and determine whether a line of data needs to be returned to a user by returning a true or false value.
When processing a database access request, multiple predicates are typically required to be executed. In the related art, each database is usually predefined with a predicate classification, and each predicate is executed in sequence according to a preset classification sequence. Such as: in an IBM database, predicates can be divided into two categories, namely stage1 and stage2, and when executed, the stage1 predicate is executed first, and then the stage2 predicate is executed. For a plurality of predicates in a certain class, the predicates can be executed in sequence according to a sequence defined by a user (sequence of occurrence in the where condition). However, in such an implementation scheme, the execution order of the predicates depends on rules predefined by the database, and is not sensitive to the cost (such as time consumption for execution) of the operators, and in some cases, the lowest-cost execution order cannot be reached. On the other hand, the execution sequence of the predicates also depends on the input sequence of the user, and when the user wants to modify the execution sequence of the predicates, the access requests need to be modified, so that the technical threshold is high. In addition, once the execution order of the predicates is determined, the predicates cannot be changed in the execution process, and when the distribution of data stored in the database changes or the data is inclined, the execution cost of the predicates cannot be guaranteed to be always low.
Disclosure of Invention
In view of the above, the present application provides a database access method and apparatus.
Specifically, the method is realized through the following technical scheme:
a database access method, the method comprising:
determining a current execution order for predicates associated with the database access request;
acquiring execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence;
judging whether the predicate meets a sequential updating condition or not according to the execution result parameter;
and when the predicates meet the sequence updating condition, updating the current execution sequence of the predicates based on the acquired execution result parameters, and continuously executing the process of acquiring the execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence.
A database access method, the method comprising:
determining a current execution order for predicates associated with the database access request;
acquiring execution result parameters of each predicate according to a preset updating period in the process of executing the predicates based on the current execution sequence;
updating the current execution sequence of the predicates based on the acquired execution result parameters, and continuously executing the process of acquiring the execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence.
A database access apparatus, the apparatus comprising:
a first determining unit that determines a current execution order for predicates associated with the database access request;
the first sampling unit is used for acquiring execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence;
the updating judgment unit judges whether the predicate meets a sequential updating condition according to the execution result parameter;
and the first updating unit updates the current execution sequence of the predicate based on the acquired execution result parameters and informs the first sampling unit of processing when the predicate meets a sequence updating condition.
A database access apparatus, the apparatus comprising:
a second determining unit that determines a current execution order for predicates associated with the database access request;
the second sampling unit is used for acquiring execution result parameters of each predicate according to a preset updating period in the process of executing the predicates based on the current execution sequence;
and the second updating unit is used for updating the current execution sequence of the predicates based on the acquired execution result parameters, and continuously executing the process of acquiring the execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence.
As can be seen from the above description, in the process of executing the predicates based on the current execution sequence, the execution result parameters of the predicates during actual execution can be periodically collected, and when it is determined that the predicates satisfy the sequence updating condition based on the collected execution result parameters, the current execution sequence of the predicates is updated, so that the execution sequence of the predicates can be dynamically adjusted according to the real total execution cost, and the change of data distribution is self-adapted, so that the predicates always keep the minimum total execution cost in the whole execution process, the efficiency of data access is improved, and the processing resources of the system are saved.
Drawings
Fig. 1 is a flow chart illustrating a database access method according to an exemplary embodiment of the present application.
FIG. 2 is a flow chart illustrating a method for determining whether predicates satisfy a sequential update condition according to an execution result parameter according to an exemplary embodiment of the application.
Fig. 3 is a flow chart illustrating another database access method according to an exemplary embodiment of the present application.
Fig. 4 is a block diagram illustrating an exemplary embodiment of a database access device according to the present application.
Fig. 5 is a block diagram illustrating a database access device according to an exemplary embodiment of the present application.
Fig. 6 is a block diagram illustrating another database access device according to an exemplary embodiment of the present application.
Detailed Description
Reference will now be made in detail to the exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, like numbers in different drawings represent the same or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present application. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present application, as detailed in the appended claims.
The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. As used in this application and the appended claims, the singular forms "a", "an", and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should also be understood that the term "and/or" as used herein refers to and encompasses any and all possible combinations of one or more of the associated listed items.
It is to be understood that although the terms first, second, third, etc. may be used herein to describe various information, such information should not be limited to these terms. These terms are only used to distinguish one type of information from another. For example, first information may also be referred to as second information, and similarly, second information may also be referred to as first information, without departing from the scope of the present application. The word "if" as used herein may be interpreted as "at … …" or "when … …" or "in response to a determination", depending on the context.
Fig. 1 is a flow chart illustrating a database access method according to an exemplary embodiment of the present application.
Referring to fig. 1, the database access method may be applied to a server running a database server program, and includes the following steps:
step 101, determining a current execution order for predicates associated with database access requests.
In this embodiment, one database access request usually corresponds to a plurality of SQL predicates (in the following description of the present application, predicates are abbreviated). In this step, after receiving a database access request, a plurality of predicates corresponding to the database access request may be determined first, and then a current execution order, that is, an initial execution order of each predicate, may be determined according to a default execution result of each predicate. Wherein the execution result typically includes two reference dimensions: selection rate and cost. The selection rate is a rate that a parameter of an execution result returned after a corresponding predicate is executed is true, and it can be understood that the lower the selection rate of a predicate is, the stronger the filtering of the predicate is. The cost is usually the time consumption of executing the corresponding predicate.
In this embodiment, when determining the initial execution order of each predicate, the initial execution order may be determined according to a conjunctive normal form and a disjunctive normal form. For example, assume that the database access request corresponds to n predicates, P respectively1，P2，…，PnThe selection rates of the N predicates are respectively F1，F2，…，FnAt a cost of C1，C2，…，Cn. Then the conjunctive normal form P can be applied1and P2and…and PnThe calculation formula of the total cost of execution is set as:
N×(C1+F1×C2+F1×F2×C3+…+F1×F2×…×Fn-1×Cn)
setting a calculation formula of the total execution cost of the disjunctive normal form as follows:
N×(C1+(1-F1)×C2+(1-F1)×(1-F2)×C3+…+(1-F1)×(1-F2)×…×(1-Fn-1)×Cn)
in the above formula, the default cost C of each predicate is usually a preset cost, and the default selection rate F of each predicate is usually obtained based on a global statistic (NDV or histogram) of the system for different predicates. In this step, all possible execution orders of the predicates may be traversed, and then an execution order with the smallest total execution cost is determined as the initial execution order according to the above formula.
And 102, acquiring execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence.
In this embodiment, after determining the current execution order of the predicates, the predicates may be sequentially executed based on the current execution order.
In this step, in the process of executing the predicates, the execution result parameters of each predicate may be collected according to a preset sampling period. Wherein, the sampling period can be set by a developer, such as: 1 minute, etc., which the present application does not specifically limit. The execution result parameters may include two dimensions: the execution cost and the value are true and false. The execution cost of the predicate is usually time-consuming for executing the corresponding predicate. The true and false value is a value result obtained after the corresponding predicate is executed, and the value result comprises the following steps: true (T) and false (F). For example, if the condition of a predicate is C equal to 100, when the predicate is executed, if the data stored in a row of the database is 100, the value of the predicate after execution is true.
In this step, when the preset sampling period is reached, it may be determined, for all the access rows, whether to acquire the execution result parameters of each predicate in the access row according to a preset sampling ratio. The sampling proportion of the access behavior to the database row to be accessed by the access request at this time may be set by a developer, for example: 10%, 15%, etc., which the present application does not specifically limit. For example, assuming that the database access request needs to access 10000 access rows, where the sampling proportion is 10%, when a preset sampling period is reached, it may be determined, starting from a certain access row, whether to sample the access row according to a proportion of 10% for each access row in the next 100 access rows or 200 access rows, that is, whether to collect the execution result parameters of each predicate in the access row. That is, the probability that each of the 100 or 200 row access rows is sampled is 10%. In the present embodiment, for convenience of description, the line of the acquisition execution result parameter may be referred to as a sampling line. It is understood that, for the 100-line access lines, if sampling is performed at a sampling rate of 10%, the number of sampling lines of the final acquisition execution result parameter may be 10 lines, and may also be 8 lines, 9 lines, 11 lines, 12 lines, and the like.
In this step, after the execution result parameters of each predicate are collected based on the sampling period, the default execution result of each predicate may be updated based on the execution result parameters, so as to improve the accuracy of determining the initial execution sequence.
And 103, judging whether the predicate meets a sequential updating condition according to the execution result parameter.
Based on the foregoing step 102, after the execution result parameters are acquired, the execution result samples corresponding to the predicates in the sampling periods may be calculated according to the execution result parameters, then, for each predicate, an average value of the execution result samples in N consecutive sampling periods is calculated as a reference execution result, and when a change between the reference execution result and a cached execution result exceeds a preset threshold, it is determined that the predicate satisfies a sequential update condition.
And 104, when the predicate meets the sequence updating condition, updating the current execution sequence of the predicate based on the acquired execution result parameters, and returning to the step 102.
Based on the judgment result of the foregoing step 103, when the predicate satisfies the order update condition, the current execution order of the predicate may be updated based on the acquired execution result parameters. Such as: the corresponding execution result sample may be calculated according to the latest acquired execution result parameter, and the current execution order of each predicate is re-determined according to the execution result sample by using the two calculation formulas of the total execution cost provided in the foregoing step 101. Of course, the current execution order of each predicate may also be re-determined by using the two calculation formulas of the total execution cost provided in step 101 according to the reference execution result obtained by the calculation in step 103, which is not limited in this application.
In this embodiment, after the current execution sequence of each predicate is updated, each predicate may be continuously executed based on the updated execution sequence, and the step 102 is returned to continue the sampling judgment process until the database access is completed.
As can be seen from the above description, in the process of executing the predicates based on the current execution sequence, the execution result parameters of the predicates during actual execution can be periodically collected, and when it is determined that the predicates satisfy the sequence updating condition based on the collected execution result parameters, the current execution sequence of the predicates is updated, so that the execution sequence of the predicates can be dynamically adjusted according to the real total execution cost, and the change of data distribution is self-adapted, so that the predicates always keep the minimum total execution cost in the whole execution process, the efficiency of data access is improved, and the processing resources of the system are saved.
FIG. 2 is a flow diagram illustrating a determination of whether a predicate satisfies a sequential update condition based on an execution result parameter according to an exemplary embodiment of the application.
Referring to fig. 2, on the basis of the foregoing embodiment shown in fig. 1, the determining whether each predicate satisfies the sequential update condition according to the execution result parameter in this embodiment may include the following steps:
step 201, calculating execution result samples corresponding to predicates in the sampling period according to the acquired execution result parameters.
In this embodiment, based on the foregoing embodiment shown in fig. 1, the execution result parameter includes two dimensions: the execution cost and the value are true and false. The following is described for these two dimensions, respectively:
one, the execution cost.
In this embodiment, assuming that the execution cost is execution time consumption, in a certain sampling period, after the execution time consumption of each predicate in each sampling line is acquired, for each predicate, an average value of the execution time consumption of the sampling line may be calculated, and the average value is used as a value of the cost reference dimension in the execution result sample. For example, assuming that the execution time of each predicate in 9 sampling lines is collected in this sampling, the total execution time of each predicate in the 9 sampling lines can be calculated separately for each predicate, and then divided by 9 to obtain the average execution time of each predicate in the 9 sampling lines, which is used as the execution result sample of the predicate in the "cost" reference dimension.
And secondly, taking true and false values.
In this embodiment, when the value of each predicate in each sampling row is obtained through collection and is true or false, a truth table may be generated according to the value. For example, assuming that the values of the predicates in the 9 sampling rows are true and false in a certain sampling period, the truth table shown in table 1 can be generated.
| Sampling line | Predicate 1 | Predicate 2 | Predicate 3 | … | Predicate n |
| Line 1 | T | F | F | … | F |
| Line 2 | T | T | T | … | F |
| … | … | … | … | … | … |
| Line 9 | T | T | F | … | T |
TABLE 1
Referring to table 1, wherein the sampling rows are rows 1 to 9, it should be noted that rows 1 to 9 are not the row numbers of the sampling rows in the database, but are numbers sequentially arranged from 1 to 1 for convenience of description. In the table, "T" represents a true value and "F" represents a false value.
In this embodiment, after the truth table is generated, the selection rate of each predicate of the sampling period may be calculated according to the truth table, and the selection rate is used as a sample of an execution result of the predicate in a reference dimension of "selection rate". Still taking table 1 as an example, for predicate 1, assuming that, of 9 sampling rows, values of 6 sampling rows are true, and values of 3 sampling rows are false, the selection rate of predicate 1 is 6/9, which is equal to 66.7%, that is, an execution result sample of predicate 1 in the reference dimension of "selection rate" in the sampling period is 66.7%. Similarly, the selection rate of other predicates in the sampling period can be calculated, and details are not repeated herein.
It should be noted that table 1 is only an exemplary illustration, and in practical applications, the truth table shown in table 1 may not be generated, and the present application is not particularly limited thereto.
In step 201, for each sampling period, after the execution result parameters of each predicate are acquired, the execution result samples of each predicate in the sampling period in two reference dimensions, namely "cost" and "selectivity" can be calculated based on the above manner.
And step 202, calculating the average value of the execution result samples of the continuous N sampling periods as a reference execution result for each predicate.
Based on the foregoing step 201, after the execution result samples corresponding to the predicates of the sampling periods are obtained through calculation, an average value of the execution result samples of consecutive N sampling periods may be calculated as the reference execution result of the sampling periods.
For example, assuming that the value of N is 5 in this step, for sampling periods 1 to 5, the average of the execution result samples of the 5 sampling periods may be calculated as the first reference execution result. Then, also for sampling periods 2 to 6, the average of the execution result samples of these 5 sampling periods may be calculated as the second reference execution result. Similarly, for sampling periods 3 to 7, the average of the execution result samples of the 5 sampling periods may be calculated as the third reference execution result, and so on, which is not described herein any more.
Of course, in practical applications, when calculating the average of the execution result samples of N sampling periods, the sampling period may not be repeatedly calculated, for example: after calculating the average of the execution result samples of the 5 sampling periods as the first reference execution result for the sampling periods 1 to 5, the average of the execution result samples of the 5 sampling periods may be calculated as the second reference execution result for the sampling periods 6 to 10, and so on. This is not particularly limited by the present application.
It should be noted that, in this step, when calculating the average of the execution result samples for N consecutive sampling periods, two reference dimensions, namely the cost and the selection rate, are calculated respectively, that is, the reference execution result also includes two reference dimensions: cost and selectivity.
Step 203, when the change of the reference execution result and the cached execution result exceeds a preset threshold, determining that the predicate satisfies a sequential update condition.
Based on the foregoing step 202, after the reference execution result is obtained through calculation, the degree of change between the reference execution result and the cached execution result may be determined, and when the degree of change exceeds a preset threshold, it may be determined that the predicate satisfies a sequential update condition.
In this embodiment, the cached execution result is an execution result that determines the current execution order of the predicate. For example, referring to step 101, after receiving a database access request, determining an initial execution order of the predicates based on default execution results of the predicates, and then caching the default execution results. That is, in the process of one database access, the execution result of the initial cache is the default execution result for determining the initial execution sequence of each predicate. Subsequently, after updating the execution order of the predicate based on a certain execution result, the cached execution result is updated with the execution result.
In this embodiment, the variation degree of the reference execution result and the cached execution result may be a difference value, a ratio of increase or decrease, and the like, which is not particularly limited in this application. It should be noted that, in this step, when a change of at least one reference dimension between the reference execution result and the cached execution result in the cost and the selection rate exceeds a preset threshold, it may be determined that the predicate satisfies the sequential update condition. In addition, since the reference execution result of each reference dimension includes the reference execution results of all predicates, in practical application, when the degree of change between the reference execution result of one or a preset number of predicates and the execution result of the cached corresponding predicate is greater than a preset threshold, it may be determined that the dimension satisfies the sequential update condition.
For example, assuming that the number of predicates related to the access request is n, where n is a natural number greater than 1, in this step, when there are m predicates in which a change between a reference execution result of at least one reference dimension of the predicate and an execution result of a cached corresponding predicate exceeds a corresponding preset threshold, it may be determined that the predicates satisfy a sequential update condition, and m is a natural number greater than or equal to 1. The value of m and the preset threshold value may be set by a developer, and this application is not particularly limited thereto.
Based on the embodiment shown in fig. 1, when the predicate is determined to satisfy the order update condition, the current execution order of the predicate may be updated based on the collected execution result parameters. When the current execution order of the search predicate is updated, in addition to the manner described in the foregoing step 104, the current execution order of the predicate may also be updated by using the reference execution result calculated in step 202 in this embodiment, which is not limited in this application. In this embodiment, after updating the current execution order of the predicate, the cached execution result is updated with the execution result that determines the current execution order, so as to be used as a reference for determining the next order update condition.
In this embodiment, when it is determined that the predicate does not satisfy the sequential update condition, it may be continuously determined whether the predicate satisfies the sequential update condition according to the next reference execution result obtained by the calculation in step 202 until the database access is finished.
Fig. 3 is a flow chart illustrating another database access method according to an exemplary embodiment of the present application.
Referring to fig. 1, the database access method may be applied in a server, and includes the following steps:
step 301, a current execution order is determined for predicates associated with database access requests.
In this embodiment, this step may be implemented by referring to the technical solution provided in step 101, which is not described herein any more.
And 302, acquiring execution result parameters of each predicate according to a preset updating period in the process of executing the predicates based on the current execution sequence.
In this embodiment, after determining the current execution order of the predicates, the predicates may be sequentially executed based on the current execution order. In this step, in the process of executing the predicates, the execution result parameters of each predicate may be collected according to a preset update period. The updating period can be set by a developer, and the updating period is a period for updating the execution sequence of the predicates. The acquisition manner and the execution result parameter may refer to the description in the embodiment shown in fig. 1, and are not described in detail herein.
And 303, updating the current execution sequence of the predicate based on the acquired execution result parameters, and returning to 302.
Based on the foregoing step 302, after the execution result parameters of each predicate are acquired, the corresponding execution result may be calculated according to the execution result parameters, and the calculation process may refer to the foregoing process of calculating the execution result sample. After the corresponding execution result is obtained through calculation, the current execution sequence of the predicates may be updated based on the execution result, each predicate is continuously executed based on the updated current execution sequence, and the step 302 is returned until the database access is completed.
As can be seen from the above description, in the process of executing the predicates based on the current execution sequence, the execution result parameters of the predicates during actual execution can be periodically collected, and the current execution sequence of the predicates is updated based on the collected execution result parameters, so that the execution sequence of the predicates can be dynamically adjusted periodically according to the actual total execution cost, the total execution cost is relatively low, the efficiency of data access is improved, and the processing resources of the system are saved.
Corresponding to the embodiment of the database access method, the application also provides an embodiment of the database access device.
The embodiment of the database access device can be applied to a server. The device embodiments may be implemented by software, or by hardware, or by a combination of hardware and software. Taking a software implementation as an example, as a logical device, the device is formed by reading corresponding computer program instructions in the nonvolatile memory into the memory for operation through the processor of the server where the device is located. In terms of hardware, as shown in fig. 4, the hardware structure diagram of the server where the database access device is located in the present application is shown, except for the processor, the memory, the network interface, and the nonvolatile memory shown in fig. 4, the server where the device is located in the embodiment may also include other hardware according to the actual function of the server, which is not described again.
Fig. 5 is a block diagram illustrating a database access device according to an exemplary embodiment of the present application.
Referring to fig. 5, the database access apparatus 500 can be applied to the server shown in fig. 4, and includes: a first determining unit 501, a first sampling unit 502, an update judging unit 503, a first updating unit 504, and a buffer updating unit 505. The update determining unit 503 may further include: a sample calculation subunit 5031, a reference calculation subunit 5032, and an update determination subunit 5033.
The first determining unit 501 determines a current execution order for predicates related to database access requests;
the first sampling unit 502 is configured to, in a process of executing the predicates based on the current execution sequence, acquire execution result parameters of each predicate according to a preset sampling period;
the update judging unit 503 judges whether the predicate satisfies a sequential update condition according to the execution result parameter;
the first updating unit 504, when the predicate satisfies a sequential updating condition, updates a current execution sequence of the predicate based on the acquired execution result parameters, and notifies the first sampling unit to process the predicate.
The sample calculation subunit 5031 calculates execution result samples corresponding to predicates in the sampling period according to the acquired execution result parameters;
the reference calculation sub-unit 5032, for each predicate, calculates a mean of execution result samples of consecutive N sampling periods as a reference execution result, where N is a natural number greater than 1;
the update determining subunit 5033, when the change between the reference execution result and the cached execution result exceeds a preset threshold, determining that the predicate satisfies a sequential update condition;
wherein the cached execution result is an execution result of determining the current execution order of the predicate;
the cache updating unit 505 updates the current execution order of the predicate based on the acquired execution result parameters, and then updates the cached execution result after determining the execution result by using the acquired execution result parameters.
Optionally, the execution result parameter includes two dimensions: the execution cost and the value are true and false;
the execution result sample and the reference execution result each include two reference dimensions: cost and a selection rate, wherein the selection rate is determined by the value true or false;
the update determination sub-unit 5033 determines that the predicate satisfies a sequential update condition when the reference execution result and the cached execution result change by more than a preset threshold in one or more reference dimensions.
Optionally, when the execution result parameter is true or false, the sample calculation subunit 5031 generates a truth table according to the collected true or false value, where the truth table records values of the predicates after execution on each sampling row, and calculates the selection rate of each predicate in the corresponding sampling period according to the truth table; and when the execution result parameter is the execution cost, calculating the average value of the execution cost of each predicate in each sampling line as the cost of each predicate in the corresponding sampling period.
Optionally, after receiving the database access request, the first determining unit 501 determines the current execution order according to the default execution result of each predicate, and caches the default execution result.
Optionally, in the process of executing the predicates based on the current execution order, when a preset sampling period is reached, the first sampling unit 502 determines, according to a preset sampling ratio, whether to acquire execution result parameters of each predicate in the access row or not, for all the access rows.
Fig. 6 is a block diagram illustrating another database access device according to an exemplary embodiment of the present application.
Referring to fig. 6, the database access apparatus 600 can be applied to the server shown in fig. 4, and includes: a second determining unit 601, a second sampling unit 602, and a second updating unit 603.
The second determining unit 601 determines a current execution order for predicates related to the database access request;
the second sampling unit 602, during the process of executing the predicates based on the current execution sequence, acquires execution result parameters of each predicate according to a preset update period;
the second updating unit 603 updates the current execution order of the predicates based on the acquired execution result parameters, and continues to perform a process of acquiring the execution result parameters of each predicate according to a preset sampling period in a process of executing the predicates based on the current execution order.
The implementation process of the functions and actions of each unit in the above device is specifically described in the implementation process of the corresponding step in the above method, and is not described herein again.
For the device embodiments, since they substantially correspond to the method embodiments, reference may be made to the partial description of the method embodiments for relevant points. The above-described embodiments of the apparatus are merely illustrative, and the units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the scheme of the application. One of ordinary skill in the art can understand and implement it without inventive effort.
The above description is only exemplary of the present application and should not be taken as limiting the present application, as any modification, equivalent replacement, or improvement made within the spirit and principle of the present application should be included in the scope of protection of the present application.
Claims (12)
1. A database access method, the method comprising:
determining a current execution order for predicates associated with the database access request;
acquiring execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence;
judging whether the predicate meets a sequential updating condition or not according to the execution result parameter;
when the predicates meet the sequence updating condition, updating the current execution sequence of the predicates based on the acquired execution result parameters, and continuously executing the process of acquiring the execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence;
wherein determining a current execution order for predicates associated with database access requests comprises:
and after receiving a database access request, determining the current execution sequence according to the default execution result of each predicate.
2. The method of claim 1, further comprising: caching the default execution result;
the judging whether the predicate meets a sequential updating condition according to the execution result parameters includes:
calculating execution result samples corresponding to each predicate in the sampling period according to the acquired execution result parameters;
calculating the average value of execution result samples of N continuous sampling periods as a reference execution result aiming at each predicate, wherein N is a natural number greater than 1;
when the change of the reference execution result and the cached execution result exceeds a preset threshold value, determining that the predicate meets a sequential updating condition;
wherein the cached execution result is an execution result of determining the current execution order of the predicate;
the method further comprises the following steps:
and after updating the current execution sequence of the predicate based on the acquired execution result parameters, determining an execution result by using the acquired execution result parameters, and then updating the cached execution result.
3. The method of claim 2,
the execution result parameters include two dimensions: the execution cost and the value are true and false;
the execution result sample and the reference execution result each include two reference dimensions: cost and a selection rate, wherein the selection rate is determined by the value true or false;
when the change between the reference execution result and the cached execution result exceeds a preset threshold, determining that the predicate satisfies a sequential update condition includes:
and when the change of the reference execution result and the cached execution result in one or more reference dimensions exceeds a preset threshold, determining that the predicate meets a sequential updating condition.
4. The method of claim 3, wherein the computing the execution result sample corresponding to each predicate of the sampling period according to the collected execution result parameters comprises:
when the execution result parameter is true or false, generating a truth table according to the collected true or false values, wherein the truth table records the values of predicates after execution on each sampling line;
calculating the selection rate of each predicate of the corresponding sampling period according to the truth table;
and when the execution result parameter is the execution cost, calculating the average value of the execution cost of each predicate in each sampling line as the cost of each predicate in the corresponding sampling period.
5. The method according to claim 1, wherein the collecting the execution result parameters of each predicate according to a preset sampling period comprises:
and when a preset sampling period is reached, determining whether to acquire execution result parameters of each predicate in the access row or not according to a preset sampling proportion for all the access rows.
6. A database access method, the method comprising:
determining a current execution order for predicates associated with the database access request;
acquiring execution result parameters of each predicate according to a preset updating period in the process of executing the predicates based on the current execution sequence;
updating the current execution sequence of the predicates based on the acquired execution result parameters, and continuously executing the process of acquiring the execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence;
wherein determining a current execution order for predicates associated with database access requests comprises:
and after receiving a database access request, determining the current execution sequence according to the default execution result of each predicate.
7. A database access apparatus, the apparatus comprising:
a first determining unit that determines a current execution order for predicates associated with the database access request;
the first sampling unit is used for acquiring execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence;
the updating judgment unit judges whether the predicate meets a sequential updating condition according to the execution result parameter;
the first updating unit updates the current execution sequence of the predicate based on the acquired execution result parameters when the predicate meets a sequence updating condition, and informs the first sampling unit of processing;
the first determining unit determines the current execution sequence according to default execution results of the predicates after receiving the database access request.
8. The apparatus according to claim 7, wherein the first determining unit buffers the default execution result;
the update judgment unit includes:
the sample calculation subunit calculates execution result samples corresponding to each predicate in the sampling period according to the acquired execution result parameters;
a reference calculation subunit, for each predicate, calculating an average of execution result samples of consecutive N sampling periods as a reference execution result, where N is a natural number greater than 1;
the updating determining subunit is used for determining that the predicate meets a sequential updating condition when the change of the reference execution result and the cached execution result exceeds a preset threshold;
wherein the cached execution result is an execution result of determining the current execution order of the predicate;
the device further comprises:
and the cache updating unit updates the cached execution result after determining the execution result by using the acquired execution result parameters after updating the current execution sequence of the predicate based on the acquired execution result parameters.
9. The apparatus of claim 8,
the execution result parameters include two dimensions: the execution cost and the value are true and false;
the execution result sample and the reference execution result each include two reference dimensions: cost and a selection rate, wherein the selection rate is determined by the value true or false;
the updating determining subunit determines that the predicate satisfies a sequential updating condition when the reference execution result and the cached execution result change in one or more reference dimensions over a preset threshold.
10. The apparatus of claim 9,
the sample calculation subunit generates a truth table according to the collected true and false values when the execution result parameter is true and false, the truth table records the values of the predicates after the execution of the predicates in the sampling rows, and the selection rate of each predicate corresponding to the sampling period is calculated according to the truth table; and when the execution result parameter is the execution cost, calculating the average value of the execution cost of each predicate in each sampling line as the cost of each predicate in the corresponding sampling period.
11. The apparatus of claim 7,
and the first sampling unit determines whether to collect the execution result parameters of each predicate in the access row according to a preset sampling proportion for all the access rows when a preset sampling period is reached in the process of executing the predicates based on the current execution sequence.
12. A database access apparatus, the apparatus comprising:
a second determining unit that determines a current execution order for predicates associated with the database access request;
the second sampling unit is used for acquiring execution result parameters of each predicate according to a preset updating period in the process of executing the predicates based on the current execution sequence;
the second updating unit is used for updating the current execution sequence of the predicates based on the acquired execution result parameters and continuously executing the process of acquiring the execution result parameters of each predicate according to a preset sampling period in the process of executing the predicates based on the current execution sequence;
and the second determining unit determines the current execution sequence according to default execution results of the predicates after receiving the database access request.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610653819.7A CN106874332B (en) | 2016-08-10 | 2016-08-10 | Database access method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610653819.7A CN106874332B (en) | 2016-08-10 | 2016-08-10 | Database access method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106874332A CN106874332A (en) | 2017-06-20 |
| CN106874332B true CN106874332B (en) | 2020-06-30 |
Family
ID=59238909
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610653819.7A Active CN106874332B (en) | 2016-08-10 | 2016-08-10 | Database access method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106874332B (en) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107506380B (en) * | 2017-07-21 | 2019-03-26 | 北京金堤科技有限公司 | A kind of method and server updating business data |
| CN107908791A (en) * | 2017-12-12 | 2018-04-13 | 郑州云海信息技术有限公司 | Data cache method, device, equipment and storage medium in distributed memory system |
| CN113626491B (en) * | 2020-05-09 | 2023-08-04 | 杭州海康威视数字技术股份有限公司 | Data query method, device and distributed data query system |
| CN115827930B (en) * | 2023-02-15 | 2023-05-05 | 杭州悦数科技有限公司 | Data query optimization method, system and device for graph database |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101105802A (en) * | 2007-06-08 | 2008-01-16 | 北京神舟航天软件技术有限公司 | Method for realizing two-dimensional predicate selectivity estimation by using wavelet-based compressed histogram |
| WO2009069882A1 (en) * | 2007-11-29 | 2009-06-04 | Electronics And Telecommunications Research Institute | Sensor network managing apparatus and method thereof |
| CN102663114A (en) * | 2012-04-17 | 2012-09-12 | 中国人民大学 | Database inquiry processing method facing concurrency OLAP (On Line Analytical Processing) |
| CN104216891A (en) * | 2013-05-30 | 2014-12-17 | 国际商业机器公司 | Method and equipment for optimizing query statement in relational database |
| US9262457B2 (en) * | 2013-08-13 | 2016-02-16 | Sybase, Inc. | On-demand hash index |
-
2016
- 2016-08-10 CN CN201610653819.7A patent/CN106874332B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101105802A (en) * | 2007-06-08 | 2008-01-16 | 北京神舟航天软件技术有限公司 | Method for realizing two-dimensional predicate selectivity estimation by using wavelet-based compressed histogram |
| WO2009069882A1 (en) * | 2007-11-29 | 2009-06-04 | Electronics And Telecommunications Research Institute | Sensor network managing apparatus and method thereof |
| CN102663114A (en) * | 2012-04-17 | 2012-09-12 | 中国人民大学 | Database inquiry processing method facing concurrency OLAP (On Line Analytical Processing) |
| CN104216891A (en) * | 2013-05-30 | 2014-12-17 | 国际商业机器公司 | Method and equipment for optimizing query statement in relational database |
| US9262457B2 (en) * | 2013-08-13 | 2016-02-16 | Sybase, Inc. | On-demand hash index |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106874332A (en) | 2017-06-20 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107391692B (en) | Recommendation effect evaluation method and device | |
| CN106874332B (en) | Database access method and device | |
| US8819038B1 (en) | System and method for performing set operations with defined sketch accuracy distribution | |
| US20210319006A1 (en) | Method And System To Estimate The Cardinality Of Sets And Set Operation Results From Single And Multiple HyperLogLog Sketches | |
| US8560509B2 (en) | Incremental computing for web search | |
| EP2946527A2 (en) | Variable duration windows on continuous data streams | |
| WO2017096892A1 (en) | Index construction method, search method, and corresponding device, apparatus, and computer storage medium | |
| CN109948036B (en) | Method and device for calculating weight of participle term | |
| US20150234883A1 (en) | Method and system for retrieving real-time information | |
| US10255325B2 (en) | Extreme value computation | |
| CN111459993A (en) | Configuration updating method, device, equipment and storage medium based on behavior analysis | |
| US11640382B2 (en) | Optimizing database performance through intelligent data partitioning orchestration | |
| WO2018149085A1 (en) | Method and system for optimizing database system, electronic device, and storage medium | |
| CN113986933A (en) | Materialized view creating method and device, storage medium and electronic equipment | |
| CN116830097A (en) | Automatic linear clustering recommendation for database region maps | |
| CN110968802B (en) | Analysis method and analysis device for user characteristics and readable storage medium | |
| JP5367632B2 (en) | Knowledge amount estimation apparatus and program | |
| CN111488736A (en) | Self-learning word segmentation method and device, computer equipment and storage medium | |
| CN114238389A (en) | Database query optimization method, apparatus, electronic device, medium, and program product | |
| CN110674387A (en) | Method, apparatus, and computer storage medium for data search | |
| WO2018205391A1 (en) | Method, system and apparatus for evaluating accuracy of information retrieval, and computer-readable storage medium | |
| CN111382342B (en) | Method, device and equipment for acquiring hot search words and storage medium | |
| CN111737555A (en) | Method and device for selecting hot keywords and storage medium | |
| CN116305297B (en) | Data analysis method and system for distributed database | |
| CN111666295B (en) | Data extraction method, terminal device and computer readable storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20200921 Address after: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman Islands Patentee after: Innovative advanced technology Co.,Ltd. Address before: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman Islands Patentee before: Advanced innovation technology Co.,Ltd. Effective date of registration: 20200921 Address after: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman Islands Patentee after: Advanced innovation technology Co.,Ltd. Address before: A four-storey 847 mailbox in Grand Cayman Capital Building, British Cayman Islands Patentee before: Alibaba Group Holding Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20210210 Address after: 801-10, Section B, 8th floor, 556 Xixi Road, Xihu District, Hangzhou City, Zhejiang Province 310000 Patentee after: Ant financial (Hangzhou) Network Technology Co.,Ltd. Address before: Ky1-9008 Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman Islands, ky1-9008 Patentee before: Innovative advanced technology Co.,Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20210908 Address after: 100020 unit 02, 901, floor 9, unit 1, building 1, No.1, East Third Ring Middle Road, Chaoyang District, Beijing Patentee after: Beijing Aoxing Beisi Technology Co.,Ltd. Address before: 801-10, Section B, 8th floor, 556 Xixi Road, Xihu District, Hangzhou City, Zhejiang Province 310000 Patentee before: Ant financial (Hangzhou) Network Technology Co.,Ltd. |