A response time-based database query optimization method and device

Abstract

The application provides a response time-based database query optimization method and device, and belongs to the technical field of databases.The method is as follows: a special query response database is constructed according to a preset database construction method; an SQL query statement is acquired and syntax analysis is performed on the SQL query statement; an RBO optimization algorithm is used to optimize the parsed SQL query statement; the parsed SQL query statement is subjected to feature extraction, and the special query response database is queried according to the extracted features to determine a response time; wherein the extracted features include an SQL feature type, SQL feature information and a data volume; and the RBO-optimized SQL query statement is optimized according to the response time and a CBO optimization algorithm.The application can automatically extract the features of the SQL query statement, establish a relationship between the features and the response time, and automatically store the relationship in the special query response database, thereby providing an accurate basis for cost calculation of the CBO, improving the database query optimization capability, reducing the cost and improving the query efficiency.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and in particular to a database query optimization method and device based on response time, equipment and storage medium. BACKGROUND

[0002] With the increase of database size, the execution cost of SQL is higher and higher, and optimization execution and cost reduction become a trend. The current general method of database query optimization is rule-based optimization (Rule-Based Optimizer, RBO) or cost-based optimization (Cost-Based Optimizer, CBO).

[0003] However, the above method still has the following disadvantages:

[0004] 1. RBO is dependent on a series of rules formulated by artificial to select query plan, and cannot optimize according to specific query and data statistical information, and the rule maintenance is relatively troublesome, and a large number of optimization rules need to be maintained manually, and with the passage of time, the rules will become more and more complex, leading to difficulty in maintenance;

[0005] 2. CBO costs a high price when collecting statistical information, and needs to scan the whole table to collect complete information regularly, increasing the overhead of the database system.

[0006] 3. CBO cannot handle some special scenarios, for example, some plans contain user-defined functions, and CBO cannot judge the specific calculation complexity, and the plan generated according to CBO needs further optimization. SUMMARY

[0007] The present application provides a database query optimization method and device based on response time, equipment and storage medium, which establishes the relationship between features and response time by analyzing and extracting the features of standard SQL syntax, and automatically stores in a special query response library, providing accurate basis for cost calculation of CBO, thereby improving the ability of database query optimization, reducing cost and improving query efficiency.

[0008] In a first aspect, an embodiment of the present application provides a database query optimization method based on response time, which comprises:

[0009] constructing a special query response library according to a preset database construction method;

[0010] obtaining a SQL query statement and performing syntax analysis on the SQL query statement;

[0011] optimizing the parsed SQL query statement by using RBO optimization algorithm;

[0012] The parsed SQL query statement is subjected to feature extraction, and a special query response library is queried according to the extracted features to determine a response time;

[0013] The extracted features include a SQL feature type, SQL feature information, and data volume.

[0014] The RBO-optimized SQL query statement is optimized according to the response time and a CBO optimization algorithm.

[0015] Optionally, the special query response library is constructed according to a preset database construction method, including:

[0016] The historical SQL query statements are obtained and subjected to syntax analysis;

[0017] The parsed SQL query statement is subjected to optimization by an RBO optimization algorithm;

[0018] The parsed SQL query statement and the RBO-optimized SQL query statement are subjected to feature extraction;

[0019] The features include a SQL feature type, SQL feature information, and data volume.

[0020] The RBO-optimized SQL query statement is subjected to optimization by a CBO optimization algorithm;

[0021] The CBO-optimized SQL query statement is executed to determine a response time;

[0022] The special query response library is constructed according to the SQL feature type, SQL feature information, data volume, and response time.

[0023] Optionally, the SQL query statement is obtained and subjected to syntax analysis, including:

[0024] The SQL feature type is determined according to an operator of the SQL query statement.

[0025] Optionally, the RBO-optimized SQL query statement is optimized according to the response time and CBO optimization algorithm, including:

[0026] A first cost calculation formula is determined according to the CBO optimization algorithm in combination with a scale of a table and an index, a row cost, a calculation cost, an I / O cost, and a network cost;

[0027] The response time is added as a time dimension to the first cost calculation formula to obtain a second cost calculation formula;

[0028] A query cost is calculated according to the second cost calculation formula, and the RBO-optimized SQL query statement is optimized according to the query cost.

[0029] Optionally, the query cost is calculated according to a second cost calculation formula, and the SQL query statement optimized by the RBO is optimized according to the query cost, comprising:

[0030] The SQL query statement with the shortest response time is selected as the query statement.

[0031] Optionally, the database query optimization method based on the response time further comprises:

[0032] If the response time is not queried when querying the special query response library, the query cost is calculated according to a first cost calculation formula.

[0033] In a second aspect, an embodiment of the present application provides a database query optimization device based on the response time, comprising:

[0034] The construction module is configured to construct a special query response library according to a preset database construction method.

[0035] The acquisition module is configured to acquire a SQL query statement and perform syntax analysis on the SQL query statement.

[0036] The optimization module is configured to optimize the analyzed SQL query statement by using an RBO optimization algorithm.

[0037] The determination module is configured to extract features from the analyzed SQL query statement and query the special query response library according to the extracted features to determine the response time.

[0038] The extracted features include a SQL feature type, a SQL feature information and a data volume.

[0039] The optimization module is further configured to optimize the SQL query statement optimized by the RBO according to the response time and a CBO optimization algorithm.

[0040] In a third aspect, an embodiment of the present application provides an electronic device comprising a memory and a processor, wherein the memory stores a computer program, and the processor implements the method according to any implementation manner of the first aspect when executing the program.

[0041] In a fourth aspect, an embodiment of the present application provides a computer readable storage medium, which stores a computer program, and the computer program is executed by a processor to implement the method according to any implementation manner of the first aspect.

[0042] The present application provides a database query optimization method, device, equipment and storage medium based on the response time, and the beneficial effects of the present application are as follows:

[0043] 1. The optimization method based on response time does not require manual statistical information, reducing the cost and complexity of manual maintenance.

[0044] 2. The response time-based optimization method does not require periodic full table scans to collect relevant statistical information, and has high real-time performance. It can also directly and effectively optimize queries for some SQL statements whose execution complexity cannot be analyzed.

[0045] It should be understood that the contents described in the summary of the invention are not intended to limit the key or important features of the embodiments of the present invention, nor are they intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] The above and other features, advantages and aspects of the embodiments of the present invention will become more apparent with reference to the following detailed description in conjunction with the accompanying drawings. In the accompanying drawings, the same or similar reference numerals represent the same or similar elements.

[0047] Figure 1 This is a flow chart of a database query optimization method based on response time according to an embodiment of the present invention;

[0048] Figure 2 This is a flowchart of a method for constructing a dedicated query response library according to an embodiment of the present invention;

[0049] Figure 3 This is a flowchart of a database query optimization method based on response time according to another embodiment of the present invention;

[0050] Figure 4 Schematic diagram of the structure of a database query optimization device based on response time according to an embodiment of the present invention;

[0051] Figure 5 This is a structural diagram of an electronic device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0052] In order to enable those skilled in the art to better understand the technical solutions in one or more embodiments of this specification, the technical solutions in one or more embodiments of this specification will be clearly and completely described below in conjunction with the drawings in one or more embodiments of this specification. Obviously, the described embodiments are only part of the embodiments of this specification, not all of the embodiments. Based on one or more embodiments of this specification, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of this document.

[0053] It should be noted that the embodiments of the application described are merely to more clearly illustrate the technical solutions of the embodiments of the application, and do not constitute a limitation on the technical solutions provided by the embodiments of the application.

[0054] Figure 1 The flow chart of a database query optimization method based on response time according to an embodiment of the application is shown in Fig. 1. Figure 1 As shown in Fig. 1, it comprises:

[0055] S101, constructing a special query response library according to a preset database construction method.

[0056] Optionally, as shown in Fig. 2, constructing a special query response library according to a preset database construction method comprises: Figure 2

[0057] S201, obtaining historical SQL query statements and performing syntax analysis on the historical SQL query statements;

[0058] S202, optimizing the parsed SQL query statements by using an RBO optimization algorithm;

[0059] S203, extracting features from the parsed SQL query statements and the RBO-optimized SQL query statements;

[0060] The features include SQL feature types, SQL feature information and data volume.

[0061] S204, optimizing the RBO-optimized SQL query statements by using a CBO optimization algorithm;

[0062] S205, determining the response time by executing the CBO-optimized SQL query statements;

[0063] S206, constructing a special query response library according to the SQL feature types, the SQL feature information, the data volume and the response time.

[0064] Specifically, in the historical SQL execution process, the features described in step S203 are extracted, the response time of each feature is calculated, and the SQL feature types, the SQL feature information, the data volume and the response time are stored in the special query response library.

[0065] Exemplarily, different SQL features can be extracted according to the characteristics of the operators in the parsing and optimization stages. These SQL features are universal, and the relationship between the historical SQL query statements and the response time can be established by establishing the relationship between the SQL features and the response time of the SQL feature execution in each stage. For example, as shown in Table 1 below:

[0066] Table 1

[0067] ​

[0068] S102, acquire the SQL query statement and perform syntax analysis on the SQL query statement.

[0069] Optionally, when a user inputs a SQL query statement for data query, the user-input query statement is acquired and syntax analysis is performed on the SQL query statement, the query content and execution steps and the SQL operators used in the query statement are analyzed, and different SQL features are divided according to the SQL operators.

[0070] Optionally, the SQL operators include a From operator, a Scan operator, a Filter operator, a Join operator, a grouping operator, a sorting operator and a projection operator, a Value operator, a TopN operator and an Exchange operator.

[0071] S103, optimize the parsed SQL query statement by using an RBO optimization algorithm.

[0072] Optionally, the RBO optimization algorithm is based on a series of optimization rules artificially formulated, which are pre-set and fixed.

[0073] Optionally, the optimization rules can include traditional column pruning, maximum and minimum elimination, projection elimination, predicate pushdown and the like, and can also include some complex Join pushdown operations; the RBO takes the built-in rules as the basis for optimization, and these rules are hard-coded in the code of KaiwuDB, and the RBO selects one from the many possible algebraic transformations of the target SQL as a logical plan according to these rules.

[0074] Optionally, the RBO rule implementation of KaiwuDB includes a matching mode and a replacement mode.

[0075] The matching mode includes three parts, the first part is the name of the rule, which is used as the identification of the rule during the compilation of the Opt file, the second part is the rule scope, which is clearly divided into types in KaiwuDB, and the third part is the rule part, which is the matching condition of the rule.

[0076] Exemplarily, the matching condition can be an equal expression, the left child is not a variable, and the right child is a variable.

[0077] S104, perform feature extraction on the parsed SQL query statement and query a special query response library according to the extracted features to determine the response time.

[0078] The extracted features include a SQL feature type, a SQL feature information and a data volume.

[0079] Exemplarily, the parsed SQL query statement and the extracted feature are that the SQL feature type is projection and the SQL feature information is table name, then the special query response library is queried according to the extracted feature type and feature information, and it is obtained that the response time corresponding to the feature type is a.

[0080] S105, optimizing the RBO optimized SQL query statement according to the response time and the CBO optimization algorithm.

[0081] Optionally, the CBO optimization algorithm is based on periodically collected statistical information to optimize, and the size of the table and index, the number of rows cost, calculation cost, I / O cost and network cost are collected, and these dimensions are comprehensively considered to start the query plan with the minimum overall cost.

[0082] Specifically, in the CBO optimization stage, the extracted SQL feature is read from the cache, and the corresponding feature response time is queried from the special query response library; the response time is supplemented as a time dimension into the cost calculation formula of CBO to form a second cost calculation formula, for example: computeCost(response time, first cost calculation formula).

[0083] Exemplarily, the second cost calculation formula gives priority to the response time as the cost evaluation standard, if the corresponding response time of the SQL feature can be found in the special query response library, the response time will be used as the only condition for cost comparison; in the cost comparison, the query scheme with the shortest response time is selected; if the corresponding response time of the SQL feature cannot be found in the special query response library, the previous first cost calculation formula is used for cost evaluation.

[0084] In a more preferred embodiment, as shown in Figure 3

[0085] When the user performs the SQL query, the SQL query statement of the user is parsed and after the syntax parsing, the RBO optimization algorithm is used to optimize the SQL query statement, and at the same time of parsing and RBO optimization, the SQL feature of the SQL query statement is extracted.

[0086] The SQL feature that can be extracted includes: SQL feature type, SQL feature information and data volume.

[0087] Further, the RBO optimized query statement is CBO optimized, after the CBO optimization is executed, the SQL query statement is scheduled to be executed, and the response time of the SQL query statement is determined according to the execution result.

[0088] Each query of the user is recorded, the relationship between the SQL feature type, the SQL feature information, the data volume and the response time is recorded, and the special query response library is constructed according to the relationship.​

[0089] Exemplarily, after the special query response library is constructed, when the user performs data query again, in the CBO optimization stage, the special query response library can be queried according to the SQL feature obtained in the syntax analysis stage, the response time corresponding to the SQL feature is found, and the CBO optimization is performed in combination with the queried response time, so that the query efficiency is further improved and some problems that cannot be optimized according to the original RBO optimization algorithm and the CBO optimization algorithm are solved.

[0090] The embodiment of the application provides a database query optimization method based on response time, and the method comprises the following steps: constructing a special query response library according to a preset database construction method; obtaining a SQL query statement and performing syntax analysis on the SQL query statement; performing optimization on the analyzed SQL query statement by using an RBO optimization algorithm; extracting features of the analyzed SQL query statement and querying the special query response library according to the extracted features to determine a response time; wherein the extracted features comprise a SQL feature type, SQL feature information and a data volume; and performing optimization on the SQL query statement optimized by the RBO optimization algorithm according to the response time and a CBO optimization algorithm. The application can automatically extract features of the SQL query statement, establish a relationship between the features and the response time, and automatically store the relationship in the special query response library, thereby providing an accurate basis for cost calculation of the CBO, improving the database query optimization capability, reducing the cost and improving the query efficiency.

[0091] The following will be described in detail Figure 4 The embodiment of the application provides a device capable of executing the above-mentioned database query optimization method based on response time.

[0092] Exemplarily, Figure 4 Fig. 1 is a structural schematic diagram of a database query optimization device based on response time according to the embodiment of the application; as shown in the figure, the optimization device 40 comprises: Figure 4 A construction module 401 is configured to construct a special query response library according to a preset database construction method.

[0093] An acquisition module 402 is configured to acquire a SQL query statement and perform syntax analysis on the SQL query statement.

[0094] An optimization module 403 is configured to perform optimization on the analyzed SQL query statement by using an RBO optimization algorithm.

[0095] A determination module 404 is configured to extract features of the analyzed SQL query statement and query the special query response library according to the extracted features to determine a response time.

[0096]

[0097] ​The extracted features include: a SQL feature type, SQL feature information, and data volume.

[0098] The optimization module 403 is further configured to optimize the SQL query statement optimized by the RBO according to a response time and a CBO optimization algorithm.

[0099] Optionally, the construction module 401 is further configured to acquire historical SQL query statements and perform syntax analysis on the historical SQL query statements; optimize the analyzed SQL query statements by using an RBO optimization algorithm; perform feature extraction on the analyzed SQL query statements and the RBO-optimized SQL query statements; wherein the features include: a SQL feature type, SQL feature information, and data volume; optimize the RBO-optimized SQL query statements by using a CBO optimization algorithm; execute the CBO-optimized SQL query statements to determine a response time; and construct a special query response library according to the SQL feature type, the SQL feature information, the data volume, and the response time.

[0100] Optionally, the acquisition module 402 is further configured to determine the SQL feature type according to an operator of the SQL query statement.

[0101] Optionally, the optimization module 403 is further configured to determine a first cost calculation formula according to the CBO optimization algorithm in combination with a scale of a table and an index, a row number cost, a calculation cost, an I / O cost, and a network cost; add the response time as a time dimension to the first cost calculation formula to obtain a second cost calculation formula; calculate a query cost according to the second cost calculation formula, and optimize the RBO-optimized SQL query statement according to the query cost.

[0102] Optionally, the optimization module 403 is further configured to select a SQL query statement with the shortest response time as the query statement.

[0103] Optionally, the optimization module 403 is further configured to calculate a query cost according to the first cost calculation formula if the response time is not queried when the special query response library is queried.

[0104] The embodiment of the present application further provides a computer electronic device, Figure 5 A structural schematic diagram of an electronic device to which the embodiment of the present application can be applied is shown in FIG. 1. Figure 5As shown, the computer electronic device includes a central processing module (CPU) 501 which can perform various appropriate actions and processes in accordance with a program stored in a read only memory (ROM) 502 or a program loaded from a storage section 508 into a random access memory (RAM) 503. In the RAM 503, various programs and data required for the operation of the system are also stored. The CPU 501, the ROM 502, and the RAM 503 are connected to each other through a bus 504. An input / output (I / O) interface 505 is also connected to the bus 504.

[0105] Connected to the I / O interface 505 are an input section 506 including a keyboard, a mouse, etc.; an output section 507 including a display device such as a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 508 including a hard disk, etc.; and a communication section 509 including a network interface card such as a LAN card, a modem, etc. The communication section 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to the I / O interface 505 as necessary. A removable recording medium 511 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc. is attached to the drive 510 as necessary, so that a computer program read out therefrom is installed in the storage section 508 as necessary.

[0106] The flow charts and block diagrams in the drawings are schematic illustrations of possible architectures, functions, and operations of systems, methods, and computer program products according to various embodiments of the present application. In this regard, each block in the flow charts or block diagrams can represent a module, a segment, or a portion of code, which comprises one or more executable instructions for implementing the specified logical functions. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently or the blocks can sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and / or flow charts, and combinations thereof, can be implemented by special purpose hardware-based systems that perform the specified functions or operations, or combinations of special purpose hardware and computer instructions.

[0107] The modules described in the embodiments of the present application or the modules can be implemented by software or by hardware. The described modules or the modules can also be arranged in a processor, for example, a processor can be described as including the constructing module 401, the obtaining module 402, the optimizing module 403 and the determining module 404, wherein the names of the modules do not constitute a limitation on the modules themselves in some cases, for example, the determining module 404 can also be described as "a determining module 404 for performing feature extraction on the parsed SQL query statement and querying the special query response library according to the extracted features to determine the response time".

[0108] As another aspect, the present application also provides a computer readable storage medium, which can be the computer readable storage medium included in the above-mentioned response time-based database query optimization apparatus, or can exist independently and not be assembled into an electronic device. The computer readable storage medium stores one or more programs, which are used by one or more processors to execute the response time-based database query optimization method described in the present application.

[0109] The above description is merely preferred embodiments of the present application and a description of the principles of the applied technology. It should be understood by those skilled in the art that the scope of the present application is not limited to the technical solutions formed by the specific combinations of the above technical features, and also covers other technical solutions formed by any combinations of the above technical features or equivalent features without departing from the inventive concept. For example, the above features are replaced with the technical features disclosed in the present application (but not limited to) having similar functions to form technical solutions.

Claims

1. A database query optimization method based on response time, characterized in that: The method includes: Constructing a dedicated query response library according to a preset database construction method; Obtaining an SQL query statement and performing syntax parsing on the SQL query statement; Use RBO optimization algorithm to optimize the parsed SQL query statement; Extracting features from the parsed SQL query statement and querying the dedicated query response library based on the extracted features to determine the response time; Among them, the extracted features include: SQL feature type, SQL feature information and data volume; Optimizing the SQL query statement after RBO optimization according to the response time and CBO optimization algorithm; Optimizing the SQL query statement after RBO optimization according to the response time and CBO optimization algorithm includes: Determine a first cost calculation formula based on the CBO optimization algorithm in combination with table and index size, row cost, computational cost, I / O cost, and network cost; Adding the response time as a time dimension to the first cost calculation formula to obtain a second cost calculation formula; Calculating the query cost according to the second cost calculation formula, and optimizing the SQL query statement after RBO optimization according to the query cost; Calculating the query cost according to the second cost calculation formula, and optimizing the SQL query statement after RBO optimization according to the query cost, includes: Select the SQL query statement with the shortest response time as the query statement.

2. The database query optimization method based on response time according to claim 1, characterized in that: The dedicated query response library is constructed according to a preset database construction method, including: Obtain historical SQL query statements and perform syntax analysis on the historical SQL query statements; Use RBO optimization algorithm to optimize the parsed SQL query statement; Perform feature extraction on the parsed SQL query statements and the RBO-optimized SQL query statements; The features include: SQL feature type, SQL feature information and data volume; Use CBO optimization algorithm to optimize SQL query statements after RBO optimization; Execute the CBO-optimized SQL query to determine the response time; The dedicated query response library is constructed according to the SQL feature type, SQL feature information, data volume and response time.

3. The database query optimization method based on response time according to claim 1, characterized in that: The obtaining of the SQL query statement and performing grammatical analysis on the SQL query statement includes: The SQL feature type is determined according to an operator of the SQL query statement.

4. The database query optimization method based on response time according to claim 1, characterized in that: Also includes: If no response time is found when querying the dedicated query response database, the query cost is calculated according to the first cost calculation formula.

5. A database query optimization device based on response time, characterized in that: The device includes: A construction module, used to construct a dedicated query response library according to a preset database construction method; An acquisition module, used to acquire an SQL query statement and perform syntax analysis on the SQL query statement; The optimization module is used to optimize the parsed SQL query statement using the RBO optimization algorithm; A determination module, configured to extract features from the parsed SQL query statement and query the dedicated query response library based on the extracted features to determine the response time; Among them, the extracted features include: SQL feature type, SQL feature information and data volume; The optimization module is further configured to optimize the SQL query statement after RBO optimization according to the response time and the CBO optimization algorithm, wherein the optimization of the SQL query statement after RBO optimization according to the response time and the CBO optimization algorithm includes: Determine a first cost calculation formula based on the CBO optimization algorithm in combination with table and index size, row cost, computational cost, I / O cost, and network cost; Adding the response time as a time dimension to the first cost calculation formula to obtain a second cost calculation formula; Calculating the query cost according to the second cost calculation formula, and optimizing the SQL query statement after RBO optimization according to the query cost; Calculating the query cost according to the second cost calculation formula, and optimizing the SQL query statement after RBO optimization according to the query cost, includes: Select the SQL query statement with the shortest response time as the query statement.

6. An electronic device, characterized in that: The method comprises a memory and a processor, wherein a computer program is stored in the memory, and when the processor executes the computer program, the method according to any one of claims 1 to 4 is implemented.

7. A computer-readable storage medium, characterized in that A computer program is stored, and when the computer program is executed by a processor, the method according to any one of claims 1 to 4 is implemented.

Images