Database view processing method and device, equipment, medium and program product

Abstract

The invention provides a database view processing method and device, equipment, a medium and a program product. The method comprises the following steps: aiming at each running database operation statement, analyzing the database operation statement to generate a syntax tree; the syntax tree comprises expressions of database operation statements; extracting a first characteristic value of each expression, and obtaining a query identifier of the database operation statement according to the first characteristic value of each expression; wherein in the expressions in the database operation statement, the first feature values of the expressions meeting the conditions are the same, and the conditions include that the type is a list expression, and elements contained in the expressions are constant expressions; and generating a database view according to the query identifier of each historically executed database operation statement. According to the method, the storage space occupied by the database view is saved.

Description

Technical Field

[0001] The present application relates to database view processing technology, and in particular to a database view processing method, device, equipment, medium and program product. Background Art

[0002] During the operation of the database, a large number of database operation statements, also known as Structured Query Language (SQL) statements, are executed. The database generally uses performance analysis tools (such as TOPSQL statistical view) to display the number of times and time consumed by SQL statements during the operation of the database.

[0003] At present, the query identifier of the SQL statement is obtained by extracting the characteristic value of each expression in the SQL statement, and the TOP SQL statistical view is generated based on the query identifier. When a list expression is included in the SQL statement, multiple different query identifiers will be generated, so that multiple statistical records will be generated in the TOP SQL statistical view for SQL statements with the same grammatical structure. There is a problem that the generated TOP SQL statistical view occupies a lot of storage space. Summary of the invention

[0004] The present application provides a method, apparatus, device, medium and program product for processing a database view, so as to solve the problem that a generated TOP SQL statistical view occupies a large amount of storage space.

[0005] In a first aspect, the present application provides a method for processing a database view, comprising: parsing each database operation statement being run to generate a syntax tree; the syntax tree includes each expression of the database operation statement;

[0006] Extracting the first characteristic value of each expression, and obtaining the query identifier of the database operation statement according to the first characteristic value of each expression; wherein, among the expressions in the database operation statement, the first characteristic values ​​of the expressions that meet the conditions are the same, and the conditions include that the type is a list expression and the elements contained are all constant expressions;

[0007] Generate a database view based on the query identifiers of each database operation statement executed historically.

[0008] Optionally, extracting the first characteristic value of each expression and obtaining the query identifier of the database operation statement according to the first characteristic value of each expression specifically includes:

[0009] If the expression satisfies the condition, a preset target feature value is used as the first feature value of the expression; if the expression does not satisfy the condition, feature information of the expression is extracted, and the first feature value of the expression is obtained according to the feature information of the expression;

[0010] The first characteristic values ​​of the expressions are concatenated to obtain a query identifier of the database operation statement.

[0011] Optionally, if the expression does not satisfy the condition, extracting feature information of the expression, and obtaining a first feature value of the expression according to the feature information of the expression specifically includes:

[0012] If the expression is a list expression, and the elements contained in the list expression include constant expressions and variable expressions, then extract the characteristic value of the constant expression and extract the characteristic value of the variable expression;

[0013] The characteristic value of the constant expression is combined with the characteristic value of the variable expression to obtain a first characteristic value of the expression.

[0014] Optionally, the step of concatenating the first characteristic values ​​of the expressions to obtain a query identifier of the database operation statement specifically includes:

[0015] Concatenate the first characteristic values ​​of the expressions to obtain a first structure identifier of the database operation statement;

[0016] The first structure identifier is encrypted according to a preset encryption algorithm to obtain a query identifier of the database operation statement.

[0017] Optionally, generating a database view according to the query identifier of each database operation statement executed historically specifically includes:

[0018] For each query identifier, determine a database operation statement set corresponding to the query identifier, wherein the query identifiers of the database operation statements in the database operation statement set are all the query identifiers;

[0019] replacing the constant expression of the database operation statement in the database operation statement set corresponding to the query identifier with a preset view constant expression to obtain the view operation statement corresponding to the query identifier; and obtaining the statistical result of the view operation statement according to the database operation statement set corresponding to the query identifier;

[0020] The database view is generated according to the view operation statements corresponding to each query identifier and the corresponding statistical results.

[0021] Optionally, the method further includes: updating the database view in response to the execution of a database operation statement.

[0022] In a second aspect, the present application provides a database view processing device, comprising:

[0023] The parsing module is used to parse each database operation statement being executed and generate a syntax tree; the syntax tree includes each expression of the database operation statement.

[0024] An extraction module is used to extract the first characteristic value of each expression and obtain the query identifier of the database operation statement according to the first characteristic value of each expression; wherein, among the expressions in the database operation statement, the first characteristic value of the expressions that meet the conditions is the same, and the conditions include that the type is a list expression and the elements contained are all constant expressions.

[0025] The generation module is used to generate a database view according to the query identifiers of each database operation statement executed historically.

[0026] In a third aspect, the present application provides an electronic device, comprising: a processor, and a memory connected to the processor;

[0027] The memory stores computer-executable instructions;

[0028] The processor executes the computer-executable instructions stored in the memory to implement the method as described in any one of the first aspects.

[0029] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method as described in any one of the first aspects.

[0030] In a fifth aspect, the present application provides a computer program product, including a computer program, which implements the method described in any one of the first aspects when executed by a processor.

[0031] The database view processing method, apparatus, device, medium and program product provided in the present application parse SQL statements to generate a syntax tree composed of expressions; extract the characteristic value of each expression to obtain the query identifier of the SQL statement; when the expression type is a list expression and the elements contained in the expression are all constant expressions, the characteristic values ​​of these expressions are the same, thereby obtaining the same query identifier; when the database view is generated according to the query identifier, the statistical information of these SQL statements with the same syntax structure is merged into one statistical information, saving the storage space occupied by the database view. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0033] Figure 1 A schematic diagram of an application scenario involved in an embodiment of the present application;

[0034] Figure 2 It is a schematic diagram of the basic execution process of a SQL statement;

[0035] Figure 3 A flowchart of a method for processing a database view provided in an embodiment of the present application;

[0036] Figure 4 A schematic diagram of the structure of a syntax tree corresponding to an SQL statement;

[0037] Figure 5 A flowchart of a second method for processing a database view provided in an embodiment of the present application;

[0038] Figure 6 It is a schematic diagram of the structure of the second syntax tree corresponding to the SQL statement;

[0039] Figure 7 A schematic diagram of the structure of a database view processing device provided in an embodiment of the present application;

[0040] Figure 8 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.

[0041] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0042] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0043] During the operation of the database, a large number of database operation statements, also known as Structured Query Language (SQL) statements, are executed. The database generally uses performance analysis tools (such as TOPSQL statistical view) to display the number of times and time consumed by SQL statements during the operation of the database.

[0044] Figure 1 Schematic diagram of an application scenario involved in the embodiment of the present application. Figure 1 As shown, a database can be any computer software that stores and manages data. During the operation of the database, a large number of database operation statements, also known as SQL statements, are executed. The database view processing tool can count the executed SQL statements and generate statistical views. The statistical view can be, for example, a TOP SQL statistical view.

[0045] A possible TOP SQL statistical view generated by the database view processing tool may be shown in Table 1 below, for example.

[0046] Table 1

[0047]

[0048] Among them, queryid represents the query identifier of the SQL statement; query represents the SQL statement string; calls represents the total number of SQL statement executions; times represents the total execution time of the SQL statement. As an example, query in Table 1 only shows part of the SQL statement string.

[0049] The TOP SQL statistical view makes it easy to find the SQL statements with the most execution times or the longest execution time. From the example in Table 1, we can see that the statement with queryid 3889337793880648542 was executed 525339 times, taking a total of 841165.16 milliseconds, and the average execution time was 841165.16 / 525339 = 1.60 milliseconds. The SQL statement is the statement corresponding to query SELECT count(*)AS low_stock F….

[0050] The TOP SQL statistical view can display the number of times and time consumed by SQL statements during database operation, as well as any of the following statistical items: the user ID that executes the SQL statement, the database ID that executes the SQL statement, the number of times the SQL statement is parsed, the total time for SQL statement parsing, the number of times the SQL statement generates an execution plan, the total time for the SQL statement to generate an execution plan, the number of rows returned by the SQL statement, the total time for reading the database, the total time for writing data blocks, and other resource consumption.

[0051] However, when a SQL statement contains a list expression, for example, when the SQL statement is SELECT$1,$2…$n,SELECT*FROM T1 WHERE id in($1,$2…$n), the above statistical view will generate multiple different query identifiers due to the inconsistent number of constant expressions in the list expression, thereby generating multiple statistical records for SQL statements with the same grammatical structure in the TOP SQL statistical view. There is a problem that the generated TOP SQL statistical view occupies a large amount of storage space. A possible TOP SQL statistical view generated by the database view processing tool for a SQL statement containing a list expression can be shown in Table 2 below.

[0052] Table 2

[0053]

[0054] As shown in Table 2, the three SQL statements select*from t1 where id in($1), select*from t1 where id in($1,$2), and select*from t1 where id in($1,$2,$3) have the same grammatical structure, except that the number of constant expressions in the list expression is 1, 2, and 3 respectively. Three different statistical records will be generated in the TOP SQL statistical view, occupying more storage space.

[0055] Therefore, how to merge SQL statements with the same syntax structure into one statement for statistics to save the storage space occupied by the TOPSQL statistical view has become an urgent problem to be solved in the processing of the TOP SQL statistical view.

[0056] In view of this, the present application proposes a method for processing a database view. For SQL statements with the same grammatical structure, if only the number of constant expressions contained in the list expression or the data type of the constant expressions is different, the number and data type of the constant expressions are ignored and only one expression is used to replace them so that the characteristic values ​​of these expressions are the same, thereby obtaining the same query identifier. When a database view is generated based on the query identifier, the statistical information of these SQL statements with the same grammatical structure is merged into one statistical information to save the storage space occupied by the database view.

[0057] It should be understood that the database view processing tool and the database can be deployed on the same electronic device for execution, or can be deployed on different electronic devices for execution, which is not limited in the embodiments of the present application. Figure 1Taking the database view processing tool and the database deployed on different electronic devices as an example for explanation, the electronic device on which the database view processing tool is deployed and the electronic device on which the database is deployed can establish a communication connection through the Internet or a local area network.

[0058] It should be understood that the database view processing method of the embodiment of the present application can be used in any scenario where statistics can be implemented for SQL statements. The execution subject of the embodiment of the present application can be a database view processing tool, or an electronic device or processing system installed with the database view processing tool. The following description takes the database view processing tool as an example.

[0059] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0060] To facilitate understanding, the basic execution process of SQL statements is explained below.

[0061] Figure 2 This is a diagram of the basic execution process of a SQL statement. The SQL statement processing process is usually divided into three main stages: parsing, optimization, and execution. Each stage is handled by its specific components, which can be the SQL parser, optimizer, and executor. Users can trigger the execution of a SQL statement by inputting a SQL statement into the client. Figure 2 As shown, the basic execution process of the SQL statement may include the following steps:

[0062] S1. The parser performs lexical and grammatical analysis on SQL statements.

[0063] For example, the parser can use the lexical analysis tool lex to perform lexical analysis on the SQL statement, and divide the SQL statement into words one by one according to spaces. Some of these words are recognized as keywords, such as SELECT, JOIN, WHERE, etc.

[0064] The parser may use, for example, a syntax analysis tool, yacc, to match syntax rules according to a predefined SQL syntax tree and obtain a RawStmt structure of the original parse tree of the SQL statement type in combination with the result of lexical analysis.

[0065] S2. The parser performs semantic analysis on the original parse tree.

[0066] Based on the original parse tree RawStmt structure, the parser further analyzes the connection conditions, expressions, constants, etc., and identifies whether the relational tables and fields exist, whether they have access rights, etc., to obtain a complete query tree Query structure, also called a syntax tree.

[0067] S3. The parser rewrites the SQL statement.

[0068] Perform some rule-based SQL optimization rewrite to obtain the optimized query tree Query structure.

[0069] S4. The optimizer generates an access path for the SQL statement.

[0070] The optimizer can explore all possible access paths. These paths may include index access, sequential scan, hash join, nested loop join, etc. Each path is represented as a Path structure, which contains the specific method of accessing data and cost estimation.

[0071] S5. The optimizer generates an execution plan.

[0072] After generating all possible access paths, the optimizer will select the best access path and generate the final execution plan. This execution plan is represented as a Plan structure, usually a tree consisting of multiple plan nodes, also called a plan tree. Each node on the plan tree represents a specific operation, such as scanning nodes, connecting nodes, sorting nodes, etc. By selecting the best access path, the optimizer can generate an efficient execution plan, thereby improving query performance.

[0073] S6. The executor executes each plan node.

[0074] The executor executes each plan node in the tree layer by layer according to the Plan structure. The executor starts from the leaf node of the plan tree, and each node requests data from its child nodes. After processing the data, the child node returns the result to the parent node, also known as the previous plan node, which then performs further processing. After all plan nodes are executed, the root node obtains the final result set.

[0075] The execution plan node also counts the number of SQL statement executions, execution time, and other resource consumption.

[0076] S7. The executor returns an execution result set.

[0077] For example, the executor can return the execution results to the client that triggered the SQL statement.

[0078] At this point, the SQL statement execution is complete.

[0079] Figure 3A flowchart of a method for processing a database view provided in an embodiment of the present application is shown below. Figure 3 As shown, the method may include the following steps:

[0080] S301: For each database operation statement being executed, the database operation statement is parsed to generate a syntax tree, wherein the syntax tree includes each expression of the database operation statement.

[0081] The database operation statement can be any SQL statement, for example, a data query statement, such as SELECT, which is used to query data from a database; or, a database insert statement, such as INSERT, which is used to insert data into a database table; or, a database update statement, such as UPDATE, which is used to update existing data in a database table; or, a database delete statement, such as DELETE, which is used to delete data in a database table.

[0082] The generated syntax tree is described below.

[0083] Each type of SQL statement has its own grammatical features, and these grammatical structures can be described by a syntax tree. The syntax tree can contain all the SQL node information of the SQL statement, for example, it can include at least one item:

[0084] (1) Common Table Expressions List (CTE list), which is used to define a temporary result set that can be referenced multiple times during the execution of a query. For example, it can be With xxx AS(...).

[0085] (2) Range Table: It is used to represent a list of data sources. For example, it can be any of the following: relational table, subquery, join, function, CTE, etc. For example, it can be FROM xxx.

[0086] (3) Join Tree: used to represent join conditions, for example, xxx join xxx on xxx.

[0087] (4) Target List, also called projection list, is used to specify the columns or parts of the expression that should be included in the query results, for example, it can be SELECT xxx.

[0088] (5) Conflict handling (On Conflict) is used to add a processing action when a record conflicts. For example, it can be on conflict xxx.

[0089] (6) Returning List: used to return the result set of Insert, Delete, or Update. For example, it can be Returning INTO xxx.

[0090] (7) Group Clause: used to group the rows in the result set so that aggregate calculations can be performed on each group. For example, it can be Group By xxx.

[0091] (8) Having Qual: used to indicate the filtering condition of the group, for example, it can be HAVING(xxx).

[0092] (9) Window Clause: used to perform complex data analysis and aggregation operations, such as an OLAP window function clause, such as OVER(PARTITION BY a).

[0093] (10) Distinct Clause: used to deduplicate the output result set. For example, it can be SELECTDistinct xxx.

[0094] (11) Sort Clause: used to sort the result set, for example, Order byxxx.

[0095] (12) Limit Offset: used to limit the number of rows in the result set. For example, it can be Limitxxx offset xxx.

[0096] (13) Set operations: used to perform set operations on two or more result sets, such as subset, intersection, union, etc., for example, xxx UNION xxx.

[0097] The following uses the SELECT statement as an example to illustrate the structure of the syntax tree.

[0098] Figure 4 is a schematic diagram of a syntax tree structure corresponding to an SQL statement. For example, an SQL statement may be: [WITH with_queries] SELECT select_list FROM table_expression [sort_specification]. Figure 4As shown, the SQL statement can be decomposed into the following four main parts: common table expression list WITH with_queries, target list SELECT select_list, range table FROM table_expression, sort clause sort_specification.

[0099] Each SQL node information may include, for example, at least one expression: field (Var), constant expression, function expression, BOOL expression, sublink expression, Case expression, Array expression, Min, Max expression, Coalesce expression, Decode expression, XML expression, NULL expression, Range Table expression, JOIN expression, FROM expression, On Conflict expression, List expression, Sort expression, Grouping expression, window function expression, function table expression, set operation expression, etc.

[0100] S302. Extract the first characteristic value of each expression, and obtain the query identifier of the database operation statement according to the first characteristic value of each expression; wherein, among the expressions in the database operation statement, the first characteristic values ​​of the expressions that meet the conditions are the same, and the conditions include that the type is a list expression and the elements contained are all constant expressions.

[0101] When the expression of a database operation statement is a list expression and the elements contained in the list expression are all constant expressions, the first characteristic value of the list expression is the same. Regardless of whether the number of elements in the list expression is the same or whether the types of the contained constant expressions are the same, the first characteristic value of the list expression is the same. Therefore, the query identifier queryid of the SQL statement corresponding to the list expression is the same.

[0102] The first characteristic value of each expression is extracted, and the query identifier of the database operation statement is obtained according to the first characteristic value of each expression. The query identifier of the database operation statement obtained according to the first characteristic value of each expression can uniquely identify the database operation statement.

[0103] S303: Generate a database view according to the query identifiers of each database operation statement executed historically.

[0104] Each database operation statement executed historically may be, for example, a database operation statement within a certain time period, such as any one of: one day, one week, one month, etc. Based on the query identifiers of these database operation statements, a data view is generated, and the execution status of the database operation statements can be counted based on the query identifiers, for example, the SQL statement string corresponding to each query identifier, the total number of SQL statement executions, the total SQL statement execution time, etc. By creating a database view, it is convenient to view and analyze the execution statistics of SQL statements.

[0105] In summary, the database view processing method provided in the embodiment of the present application parses the SQL statement to generate a syntax tree composed of expressions; extracts the characteristic value of each expression to obtain the query identifier of the SQL statement. When the expression type is a list expression and the elements contained in the expression are all constant expressions, the characteristic values ​​of these expressions are the same, thereby obtaining the same query identifier. When the database view is generated according to the query identifier, the statistical information of these SQL statements with the same syntax structure is merged into one statistical information, saving the storage space occupied by the database view.

[0106] Figure 5 The flowchart of the second method for processing a database view provided in the embodiment of the present application is as follows. Figure 5 As shown, in this embodiment Figure 3 Based on the embodiment, a method for processing a database view is described in detail. The method may include the following steps:

[0107] S501, for each database operation statement being run, the database operation statement is parsed to generate a syntax tree; the syntax tree includes each expression of the database operation statement;

[0108] The following uses the SQL statement select * from t1 where id in (100, 200, 300) as an example to illustrate the generated syntax tree.

[0109] Figure 6 This is a schematic diagram of the structure of the second syntax tree corresponding to the SQL statement. Figure 6 As shown, the syntax structure of the SQL statement is divided into two parts:

[0110] 1. Target List, corresponding to the "Select*" part, is expanded to the t1.id field.

[0111] 2. Join Tree, mainly connection conditions, filtering conditions, etc.

[0112] (1) From List: This example is a single-table query, that is, "from t1"

[0113] (2)Quals(filter condition): query filter condition, i.e.: "where id in(100,200,300)"

[0114] (3)ScalarArrayOpExpr (scalar array expression): This example has only one condition: "id in (100, 200, 300)"

[0115] (4) Var(field): The left child of the above scalar array expression, representing the "t1.id" field

[0116] (5) List Expr: The right child of the above scalar array expression, which means "id in (100, 200, 300)"

[0117] (6) Const (constant): The constant values ​​of the above List Expr, which are "100", "200", and "300" respectively.

[0118] S502: extract the first characteristic value of each expression, and obtain the query identifier of the database operation statement according to the first characteristic value of each expression. As an example, S502 may include the following steps:

[0119] S5021. Determine whether the expression satisfies a condition; the condition includes that the type is a list expression and the elements contained are all constant expressions;

[0120] If yes, execute step S5022; if no, execute step S5023.

[0121] S5022. Use the preset target eigenvalue as the first eigenvalue of the expression.

[0122] In one example, a list expression (List Expr) included in a syntax tree is taken as an example. The list expression contains 3 elements, and these 3 elements are all constant expressions "100", "200" and "300". Therefore, the preset target feature value can be used as the first feature value of the expression, for example, it can be "List_Expr_n_const".

[0123] S5023. Extract feature information of the expression, and obtain a first feature value of the expression according to the feature information of the expression.

[0124] If the expression is a list expression, and the elements contained in the list expression include constant expressions and variable expressions, then the characteristic value of the constant expression is extracted, and the characteristic value of the variable expression is extracted.

[0125] Combine the eigenvalue of the constant expression with the eigenvalue of the variable expression to obtain the first eigenvalue of the expression.

[0126] In one example, take the list expression (List Expr) contained in the syntax tree as an example, if the elements contained in the list expression include 2 constant expressions "100", "200" and 1 variable expression "a". The feature values ​​of the constant expressions can be "const_integer_8_not_null" for example; the feature values ​​of the variable expressions can be "var_integer_8_not_null" for example; the first feature value can be: List_Expr_3_const_integer_8_not_null_const_integer_8_not_null_var_integer_8_not_null.

[0127] In one example, if the expression is a constant expression (const), the features that can be extracted may include: constant type, constant length, whether it is empty, etc. After these features of the constant expression are converted into corresponding strings and connected into a large string with '_', the feature value of the constant expression can be extracted. For example, if the constant type is integer; the constant length is 8; whether it is empty is not_null, the feature value of the constant expression extracted may be const_integer_8_not_null.

[0128] For example, the first feature value of "select *" is select_all; the first feature value of "from t1" is from_t1; and the first feature value of "where id in" is where_id_in.

[0129] Steps S3021 to S3023 are repeatedly executed until the first characteristic values ​​of all expressions in the syntax tree corresponding to a database operation statement are extracted.

[0130] S5024, concatenate the first characteristic values ​​of each expression to obtain a query identifier of the database operation statement. As an example, S5024 specifically includes:

[0131] 1. Concatenate the first characteristic values ​​of each expression to obtain the first structure identifier of the database operation statement;

[0132] For example, the first characteristic values ​​of each expression can be concatenated using a connector to form a large string to obtain a first structure identifier of the database operation statement, which can uniquely identify the characteristics of the SQL statement. As an example, the connector can be, for example, '_'.

[0133] Continuing to take the SQL statement select * from t1 where id in (100, 200, 300) as an example, the generated first structure identifier may be, for example, select_all_from_t1_where_id_in_List_Expr_n_const.

[0134] 2. Encrypt the first structure identifier according to a preset encryption algorithm to obtain a query identifier of the database operation statement.

[0135] The preset encryption algorithm in this example may be, for example, a hash function (Message Digest Algorithm 5, MD5), which generates a 32-bit hexadecimal number, such as "f5b4b1a1e2e2e8c3d1e3b1f1c2c3d4e5", and then the output MD5 hash value is further converted through the algorithm to obtain a query identifier of the database operation statement, such as "2842104312575158610". The above SQL statement may correspond to the unique query identifier.

[0136] Repeat step S502 to obtain query identifiers of all database operation statements executed historically.

[0137] S503: Generate a database view according to the query identifiers of each database operation statement executed historically. As an example, S503 may include the following steps:

[0138] S5031. For each query identifier, determine a database operation statement set corresponding to the query identifier, where the query identifiers of the database operation statements in the database operation statement set are all the query identifiers.

[0139] A query identifier is an identifier used to uniquely identify a specific database operation statement. Therefore, it is possible to classify according to the query identifier, and classify database operation statements with the same query identifier into the same set. In this way, the database operation statement set corresponding to the query identifier can be obtained, and SQL statements with the same structure can be classified into the same set.

[0140] S5032. Replace the constant expression of the database operation statement in the database operation statement set corresponding to the query identifier with a preset view constant expression to obtain the view operation statement corresponding to the query identifier; and, based on the database operation statement set corresponding to the query identifier, obtain the statistical result of the view operation statement.

[0141] For example, if a SQL statement contains constant expressions, you can use preset view constant expressions to replace them in the TOP SQL view. For example, you can use the placeholder $n to replace them. For example, for the following SQL statement:

[0142] select*from t1 where id=1;

[0143] select*from t1 where id=2;

[0144] select*from t1 where id=3;

[0145] select*from t1 where id=4;

[0146] When adding to the TOP SQL view, the corresponding view operation statement can be:

[0147] select*from t1 where id=$1;

[0148] One possible replacement is as follows:

[0149] 1. When parsing SQL statements, the location and length information of constant expressions can be recorded in the Query data structure.

[0150] 2. Based on the SQL statement string, traverse the constant expression list and replace the constant expressions for the starting position and length with $n one by one.

[0151] 3. After completion, all constant expressions in the SQL statement are replaced by $1, $2, …, $n.

[0152] A possible TOP SQL view generated for SQL statements containing constant expressions can be shown in Table 3 below:

[0153] Table 3

[0154]

[0155] Another example: When a SQL statement contains list expressions, you can use preset view constant expression replacements for them in the TOP SQL view, such as using the placeholder $n replacement, regardless of the number or type of constant expressions. For example, for the following SQL statement:

[0156] select*from t1 where id in(100,200,300);

[0157] select*from t1 where id in(100,200);

[0158] select*from t1 where id in(100);

[0159] When adding to the TOP SQL view, the corresponding view operation statement can be:

[0160] select*from t1 where id in($1);

[0161] A possible TOP SQL view generated for a SQL statement containing a list expression can be shown in Table 4 below:

[0162] Table 4

[0163]

[0164] According to the database operation statement set corresponding to the query identifier, the data in the database operation statement set can be counted, for example, the view operation statements in the set can be counted to obtain the number of executions of the corresponding view operation statements; or, the execution time of the view operation statements in the set can be accumulated to obtain the total execution time of the corresponding view operation statements, etc., so as to obtain the statistical results of the view operation statements.

[0165] S5033. Generate a database view according to the view operation statements and corresponding statistical results corresponding to each query identifier.

[0166] For example, according to different user statistical display requirements, a database view may be generated according to the view operation statements corresponding to each query identifier and the corresponding statistical results.

[0167] In one example, the user needs to display the following content through the statistical view: the query identifier queryid of the view operation statement, the string query of the view operation statement, the number of execution calls and the total execution time times; for example, the statement can be generated through the following database view: postgresql = #select queryid, query, calls, round (total_exec_time, 2) as times from sys_stat_statements.

[0168] S504: In response to the execution of the database operation statement, update the database view. As an example, S504 may specifically include:

[0169] S5041. For a newly executed database operation statement, obtain a query identifier of the database operation statement. For details, refer to steps S501-S502.

[0170] S5042: Determine whether there is a database operation statement set corresponding to the query identifier.

[0171] If yes, execute step S5043; if no, execute step S5044.

[0172] S5043. Add the database operation statement to the database operation statement set corresponding to the query identifier.

[0173] S5044. Generate a new corresponding database operation statement set for the query identifier.

[0174] S5045. Use the constant expression to replace the preset view constant expression to obtain the view operation statement corresponding to the database operation statement; and update the statistical result of the view operation statement according to the database operation statement set corresponding to the query identifier. For example, the total number of executions of the SQL statement is increased by 1, and the total execution time of the SQL statement is increased by the execution time of this database operation statement.

[0175] S5046. Update the database view according to the view operation statements corresponding to each query identifier and the corresponding updated statistical results.

[0176] By updating the database view, the execution results of the updated SQL statement can be reflected.

[0177] In summary, the database view processing method provided in the embodiment of the present application parses the SQL statement to generate a syntax tree composed of expressions; extracts the characteristic value of each expression to obtain the query identifier of the SQL statement. When the expression type is a list expression and the elements contained in the expression are all constant expressions, the characteristic values ​​of these expressions are the same, thereby obtaining the same query identifier. When the database view is generated according to the query identifier, the statistical information of these SQL statements with the same syntax structure is merged into one statistical information, saving the storage space occupied by the database view.

[0178] Next, we will continue to take the SQL statement select * from t1 where id in (100, 200, 300) as an example to explain the database view generated by the prior art and the database view generated by the database view processing method provided by the present application. Figure 6 shown.

[0179] (I) In the prior art, the feature extraction process for the list expression ListExpr in the ScalarArrayOpExpr expression in the syntax tree is as follows:

[0180] 1. Extract the characteristic value of the list expression List Expr, which may be, for example, "list_expr_n". For the SQL statement in this embodiment, the number of elements in the list expression is 3, and the characteristic value of the list expression List Expr may be "list_expr_3".

[0181] 2. Extract the characteristic values ​​of each constant expression in the list expression, including: the data type of the constant expression, the length information of the constant expression, and whether it is empty.

[0182] The characteristic values ​​of the constants "100", "200", and "300" are all "const_integer_8_not_null", for example.

[0183] 3. The characteristic value of the complete List Expr expression can be:

[0184] "list_expr_3_const_integer_8_not_null_const_integer_8_not_null_const_integer_8_not_null".

[0185] Continuing with the above SQL statement as an example, if the rest of the SQL statement remains unchanged, and if the list expression contains two constant expressions, the SQL statement is, for example: select * from t1 where id in (100, 200), the extracted feature value of the List Expr expression can be:

[0186] "list_expr_2_const_integer_8_not_null_const_integer_8_not_null".

[0187] If the list expression contains a constant expression, such as the SQL statement: select * from t1 where idin(100), the extracted feature value of the List Expr expression can be:

[0188] "list_expr_1_const_integer_8_not_null".

[0189] This will result in different feature values ​​calculated when the list expression List contains 1, 2, and 3 constant expressions, so the query identifiers of the SQL statements corresponding to the list expression are also different. As a result, the SQL statements with the same grammatical structure generate 3 different SQL statements simply because of the different number of constant expressions in the List expression. Based on the query identifiers of each database operation statement executed historically, a database view is generated, as shown in Table 5:

[0190] Table 5

[0191]

[0192] (II) Using the method provided in the embodiment of the present application, the feature extraction process for the list expression List Expr in the ScalarArrayOpExpr expression in the syntax tree is as follows:

[0193] 1. Determine whether the elements of the list expression in the syntax tree are constant expressions. If the elements of the list expression are all constant expressions, then when extracting the characteristic value of the list expression, ignore the type, length, whether it is empty, and other information of each constant expression in the list expression, as well as the number of constant expressions. It is only considered as a constant expression with type ignored. Therefore, no matter how many different constant expressions the list expression contains, the characteristic value of the list expression is the same. For the above three SQL statements, select*from t1 where id in(100,200,300), select*fromt1where id in(100,200), select*from t1 where id in(100), the characteristic value of the list expression can be, for example, "list_expr_1_const". Thus, the same query identifier is obtained. According to the query identifiers of each database operation statement executed historically, the database view generated is shown in Table 6:

[0194] Table 6

[0195]

[0196] To summarize, using the database view processing method provided in the embodiments of the present application, for SQL statements with the same structure and in which the elements of the list expressions are all constant expressions, regardless of the number of constant expressions and whether their types are the same, the statistical information of these SQL statements is merged into one SQL statement, thereby saving the storage space occupied by the database view.

[0197] Figure 7 A schematic diagram of a database view processing device provided in an embodiment of the present application. Figure 7As shown, the device may include, for example: a parsing module 701 , an extracting module 702 , and a generating module 703 .

[0198] The parsing module 701 is used to parse each database operation statement being executed and generate a syntax tree; the syntax tree includes each expression of the database operation statement.

[0199] Extraction module 702 is used to extract the first characteristic value of each expression and obtain the query identifier of the database operation statement according to the first characteristic value of each expression; wherein, among the expressions in the database operation statement, the first characteristic values ​​of the expressions that meet the conditions are the same, and the conditions include that the type is a list expression and the elements contained are all constant expressions.

[0200] The generating module 703 is used to generate a database view according to the query identifiers of each database operation statement executed historically.

[0201] A possible implementation method, extraction module 702, is specifically used to: if the expression meets the conditions, use the preset target feature value as the first feature value of the expression; if the expression does not meet the conditions, extract the feature information of the expression, and obtain the first feature value of the expression based on the feature information of the expression; splice the first feature values ​​of each expression to obtain the query identifier of the database operation statement.

[0202] A possible implementation method, extraction module 702, is specifically used for: if the expression is a list expression, and the elements contained in the list expression include constant expressions and variable expressions, then extract the characteristic value of the constant expression, extract the characteristic value of the variable expression; merge the characteristic value of the constant expression with the characteristic value of the variable expression to obtain the first characteristic value of the expression.

[0203] In a possible implementation, the extraction module 702 is specifically used to: concatenate the first characteristic values ​​of each expression to obtain a first structure identifier of the database operation statement; and encrypt the first structure identifier according to a preset encryption algorithm to obtain a query identifier of the database operation statement.

[0204] In a possible implementation, the generating module 703 is specifically configured to: for each query identifier, determine a database operation statement set corresponding to the query identifier, wherein the query identifiers of the database operation statements in the database operation statement set are all the query identifiers;

[0205] replacing the constant expression of the database operation statement in the database operation statement set corresponding to the query identifier with the preset view constant expression to obtain the view operation statement corresponding to the query identifier; and obtaining the statistical result of the view operation statement according to the database operation statement set corresponding to the query identifier;

[0206] Generate a database view according to the view operation statements and corresponding statistical results corresponding to each query identifier.

[0207] In a possible implementation, the generation module 703 is further configured to: respond to the execution of a database operation statement and update the database view.

[0208] The device provided in the embodiment of the present application can execute the actions in the above-mentioned database view processing method embodiment, and its implementation principle and technical effect are similar, which will not be repeated here.

[0209] Figure 8 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application. Figure 8 As shown, the electronic device may include: at least one processor 801 and a memory 802 .

[0210] The memory 802 is used to store programs. Specifically, the programs may include program codes, and the program codes include computer operation instructions.

[0211] The memory 802 may include a high-speed RAM memory, and may also include a non-volatile memory (non-volatile memory), such as at least one disk memory.

[0212] The processor 801 is used to execute the computer-executable instructions stored in the memory 802 to implement the actions in the aforementioned method embodiment. The processor 801 may be a central processing unit (CPU), or an application specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application.

[0213] Optionally, the electronic device may further include a communication interface 803 for communicating with an external device. In a specific implementation, if the communication interface 803, the memory 802 and the processor 801 are implemented independently, the communication interface 803, the memory 802 and the processor 801 may be interconnected via a bus and communicate with each other.

[0214] Optionally, in a specific implementation, if the communication interface 803, the memory 802 and the processor 801 are integrated on a chip, the communication interface 803, the memory 802 and the processor 801 can communicate through an internal interface.

[0215] The present application also provides a computer-readable storage medium, which may include: a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a disk or an optical disk, and other media that can store program codes. Specifically, the computer-readable storage medium stores program instructions, and the program instructions are used to implement the actions of the implementation method of the above-mentioned database view processing method.

[0216] The present application also provides a computer program product, which includes an execution instruction, which is stored in a readable storage medium. At least one processor of an electronic device can read the execution instruction from the readable storage medium, and at least one processor executes the execution instruction so that the electronic device implements the actions of the above-mentioned database view processing method implementation method.

[0217] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the following claims.

[0218] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.

Claims

1. A method for processing a database view, characterized in that: include: For each database operation statement being run, the database operation statement is parsed to generate a syntax tree; The syntax tree includes each expression of the database operation statement; Extracting the first characteristic value of each expression, and obtaining the query identifier of the database operation statement according to the first characteristic value of each expression; wherein, among the expressions in the database operation statement, the first characteristic values ​​of the expressions that meet the conditions are the same, and the conditions include that the type is a list expression and the elements contained are all constant expressions; Generate a database view based on the query identifiers of each database operation statement executed historically.

2. The method according to claim 1, characterized in that The extracting the first characteristic value of each expression and obtaining the query identifier of the database operation statement according to the first characteristic value of each expression specifically includes: If the expression satisfies the condition, a preset target feature value is used as the first feature value of the expression; if the expression does not satisfy the condition, feature information of the expression is extracted, and the first feature value of the expression is obtained according to the feature information of the expression; The first characteristic values ​​of the expressions are concatenated to obtain a query identifier of the database operation statement.

3. The method according to claim 2, characterized in that If the expression does not satisfy the condition, extracting feature information of the expression, and obtaining a first feature value of the expression according to the feature information of the expression, specifically includes: If the expression is a list expression, and the elements contained in the list expression include constant expressions and variable expressions, then extract the characteristic value of the constant expression and extract the characteristic value of the variable expression; The characteristic value of the constant expression is combined with the characteristic value of the variable expression to obtain a first characteristic value of the expression.

4. The method according to claim 2, characterized in that: The step of concatenating the first characteristic values ​​of the expressions to obtain a query identifier of the database operation statement specifically includes: Concatenate the first characteristic values ​​of the expressions to obtain a first structure identifier of the database operation statement; The first structure identifier is encrypted according to a preset encryption algorithm to obtain a query identifier of the database operation statement.

5. The method according to claim 1, characterized in that The generation of a database view according to the query identifier of each database operation statement executed historically specifically includes: For each query identifier, determine a database operation statement set corresponding to the query identifier, wherein the query identifiers of the database operation statements in the database operation statement set are all the query identifiers; replacing the constant expression of the database operation statement in the database operation statement set corresponding to the query identifier with a preset view constant expression to obtain the view operation statement corresponding to the query identifier; and obtaining the statistical result of the view operation statement according to the database operation statement set corresponding to the query identifier; The database view is generated according to the view operation statements corresponding to each query identifier and the corresponding statistical results.

6. The method according to any one of claims 1 to 5, characterized in that: Also includes: In response to the execution of the database operation statement, the database view is updated.

7. A database view processing device, characterized in that: The device comprises: A parsing module, for parsing each database operation statement being run, and generating a syntax tree; the syntax tree includes each expression of the database operation statement; An extraction module, used to extract the first characteristic value of each expression, and obtain the query identifier of the database operation statement according to the first characteristic value of each expression; wherein, among the expressions in the database operation statement, the first characteristic value of the expressions that meet the conditions is the same, and the conditions include that the type is a list expression and the elements contained are all constant expressions; The generation module is used to generate a database view according to the query identifiers of each database operation statement executed historically.

8. An electronic device, characterized in that: include: A processor, and a memory connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.

10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed by a processor.

Images