Database query optimization method and system based on general relation view

Abstract

The invention discloses a database query optimization method and system based on a general relation view. According to the method and the system, a query instruction input by a user in a similar natural language is received, and a database table associated with target attributes and conditions is automatically analyzed; a weighted graph model is constructed based on table relation topology, an improved path optimization algorithm is adopted to dynamically generate a table connection sequence, and a path selection mechanism preferentially minimizes the calculation complexity of connection operation; for a cross-multi-table query scene, generating an optimal connection path through a global model; finally, an optimization result is converted into a standard database query statement to be executed. Compared with the prior art, the method and the system have the advantages that the user operation threshold is obviously reduced, non-technical personnel can directly realize complex query through attribute names, the multi-table connection query efficiency is effectively improved, and the performance is close to that of native query in a large database environment; and a mainstream database system can be compatible through a middleware framework, so that the method has wide applicability.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database management, in particular to a database query optimization method and system based on universal relation view. BACKGROUND

[0002] The current mainstream scheme for simplifying database queries at home and abroad includes universal relation view (URV) language and graph path algorithm / graph traversal algorithm, wherein: the URV language allows users to query directly based on attribute names through the "RETRIEVE-WHERE" instruction, but there is no effective mechanism to solve the optimization of multi-table connection paths, resulting in a dependence on the default connection strategy of the database during execution, which is prone to redundant calculations in complex queries; the traditional graph path algorithm / graph traversal algorithm can realize table relationship traversal, but its weight design is not adapted to the characteristics of the database, and the influence of the number of table rows on the time complexity of the connection operation is not considered, and when multiple target tables are connected, simple path superposition is used, and there is a lack of global cost optimization mechanism, especially when the number of rows of associated tables is significantly different, the performance is significantly degraded.

[0003] If the above technologies are combined, there are still fundamental defects, mainly reflected in that non-technical users obtain input simplification, but cannot guarantee query efficiency, especially in large-scale database multi-table query scenarios, the response is slow, and a new scheme that takes into account ease of use and execution optimization is urgently needed. SUMMARY

[0004] The technical purpose of the present application is to provide a database query optimization method and system based on universal relation view, which takes into account the ease of use and execution efficiency of database queries.

[0005] To achieve the above technical purpose:

[0006] In a first aspect, the present application provides a database query optimization method based on universal relation view, characterized in that it comprises the following steps:

[0007] Query instruction analysis, automatically identifying the database tables associated with the target attributes and conditions contained in the query instruction, and obtaining a target table set;

[0008] A weighted graph is constructed with database tables as nodes and node weights as real-time row numbers of the database tables, and for the multi-table query scenario of the target table set, the optimal path is selected according to the lowest global cost; the composition of the global cost includes the cumulative term of the single-step connection cost of each node in the path and the connected multiplication term of the minimum number of rows P min to each node, and the single-step connection cost is the smaller value of the adjacent table real-time row number and the minimum number of rows P min of the current node, and the minimum number of rows P min of the current node is the real-time row number of the current node and the minimum number of rows Pmin the smaller value of infinity and the real number of rows of the path starting node min the smaller value of infinity and the real number of rows of the path starting node

[0009] optimizing the table connection sequence according to the selected optimal path, generating a standard database query statement based on the optimized table connection sequence, and executing the standard database query statement.

[0010] Further, the query instruction parsing includes a syntax checking process, and the checking mechanism includes query instruction structure integrity checking and attribute table name prefix legality verification.

[0011] Further, the query instruction parsing further includes the following sub-steps:

[0012] The target attribute is mapped to each database table field through a dynamic dictionary, and an identical attribute ambiguity resolution mechanism is constructed.

[0013] The condition expression of WHERE in the query instruction is converted into a logical tree structure.

[0014] The reachability between tables is verified based on the primary key and foreign key constraint conditions.

[0015] Further, the generation and execution of the standard database query statement include the following sub-steps:

[0016] The primary key conditions of each table are automatically derived based on the optimized table connection sequence, and a JOIN chain is constructed.

[0017] The logical tree is converted into an SQL WHERE clause, and a null safety constraint condition is added.

[0018] Different database SQL dialects are adapted through a language conversion rule library.

[0019] As a better implementation, the logical tree structure is a Steiner tree structure.

[0020] In a second aspect, based on a general design concept, the application further provides a database query optimization system based on a general relationship view, characterized in that it comprises:

[0021] A user interface module for providing a query instruction input interface and receiving a query instruction.

[0022] A semantic parsing engine for parsing target attributes and conditions in the query instruction, automatically identifying database tables associated with the target attributes and conditions, and generating a target table set.

[0023] A dynamic optimizer is configured to build a weighted graph with database tables as nodes and node weights as real-time row numbers of the database tables, and select an optimal path with the lowest global cost for a multi-table query scenario of a target table set; the global cost is composed of an accumulated item of single-step connection costs of each node in the path and a multiplied item of the minimum row number P of the path when reaching each node, where the single-step connection cost is the smaller value of the real-time row number of an adjacency table and the minimum row number P of the path of the current node min The minimum row number P of the path of the current node is the smaller value of the real-time row number of the current node and the minimum row number P of the path of the previous node min The minimum row number P of the path of the current node is the smaller value of the real-time row number of the current node and the minimum row number P of the path of the previous node min The minimum row number P of the path of the current node is the smaller value of the real-time row number of the current node and the minimum row number P of the path of the previous node min The minimum row number P of the path of the current node is the smaller value of the real-time row number of the current node and the minimum row number P of the path of the previous node min The minimum row number P of the path of the current node is the smaller value of the real-time row number of the current node and the minimum row number P of the path of the previous node

[0024] An SQL converter is configured to optimize a table connection sequence according to the optimal path, generate a standard database query statement based on the optimized table connection sequence, and submit the standard database query statement to a bottom database engine for execution.

[0025] Further, the user interface module comprises the following components:

[0026] An instruction input area is configured to provide a structured query instruction input form;

[0027] A virtual view generator is configured to dynamically render a general relationship view, which includes all table fields displayed according to attribute names and marked primary-foreign key relationships;

[0028] A result display panel is configured to output a query result.

[0029] Further, the semantic analysis engine comprises the following units:

[0030] An attribute-table mapping unit is configured to map target attributes to database table fields through a dynamic dictionary, and build an attribute name ambiguity resolution mechanism;

[0031] A condition expression parse tree generator is configured to convert a condition expression of WHERE in a query instruction into a logic tree structure;

[0032] A table connection relationship verifier is configured to verify reachability between tables based on primary-foreign key constraint conditions.

[0033] Further, the dynamic optimizer comprises the following units:

[0034] A weighted graph building unit is configured to build a weighted graph with database tables as nodes and node weights as real-time row numbers of the database tables;

[0035] A path selection engine is configured to adjust a dynamic calculation model, which includes calculation of a single-step connection cost of each node and a path minimum row number P corresponding to each node. min

[0036] A real-time performance monitor is configured to adjust a calculation strategy of a global cost, which is composed of an accumulated item of a single-step connection cost of each node in a path and a multiplied item of a path minimum row number P when reaching each node, and the adjustment of the calculation strategy is achieved by multiplying a corresponding coefficient with the accumulated item and the multiplied item. min

[0037] An output verification interface is configured to generate an optimal path report based on a minimum global cost.

[0038] Further, the SQL converter includes:

[0039] A JOIN chain construction engine is configured to establish a path sequence mapping rule and implement a connection type selection.

[0040] A conditional expression injector is configured to accurately convert a logic tree of the semantic analysis engine into a SQL statement condition.

[0041] A syntax adapter is configured to adapt different database SQL dialects based on a language conversion rule library, and implement cross-database dialect conversion.

[0042] The present application has the following advantages:

[0043] 1) The method and system of the present application support a user to input a query instruction in a natural language, automatically parse a database table associated with a target attribute and a condition, significantly reduce a user operation threshold, and enable a non-technical person to directly implement a complex query through an attribute name.

[0044] 2) The method and system of the present application construct a weighted graph model based on a table relationship topology, dynamically generate a table connection sequence through an improved path optimization algorithm, the path selection mechanism of which preferentially minimizes a calculation complexity of a connection operation, and generates an optimal connection path through a global model for a multi-table query scenario, thereby effectively improving a multi-table connection query efficiency, and especially in a large database environment, the performance approaches that of a native query.

[0045] 3) The method and system of the present application have good compatibility and wide applicability. BRIEF DESCRIPTION OF DRAWINGS

[0046] Figure 1 is a database protection device example in the present application scheme one;

[0047] Figure 2 is a database path example diagram in the present application scheme one;

[0048] ​​Figure 3 is an example of the database path with weight in the first solution of the present application;

[0049] Figure 4 is a logical tree structure in the second solution;

[0050] Figure 5 is an example of converting the logical tree of the semantic analysis engine into an SQL condition in the second solution;

[0051] Figure 6 is a dynamic optimizer flowchart of embodiment 1;

[0052] Figure 7 is an architecture block diagram of the second solution. DETAILED DESCRIPTION

[0053] In order to further illustrate the design idea of the present application, the present application will be described in detail below in combination with the drawings and specific embodiments.

[0054] Solution 1:

[0055] A database query optimization method based on a general relationship view, the implementation process of which comprises the following steps:

[0056] Step S100: query instruction analysis

[0057] The query instruction input by the user is received and analyzed, the database tables associated with the target attributes and conditions in the query instruction are automatically identified, and a target table set is generated.

[0058] The format of the query instruction is generally "RETRIEVE [target attribute list] WHERE [condition expression]", which is a natural language-like instruction. The analysis of the query instruction is to convert the natural language-like instruction input by the user into a structured semantic model, and the specific execution process includes: (1) instruction decomposition and syntax checking; (2) attribute accurate mapping; (3) condition expression structuring; (4) target table set generation and the like.

[0059] The above execution process will be specifically introduced below in combination with code examples.

[0060] (1) Instruction decomposition and syntax checking, which is used to ensure the input format specification and at the same time establish a syntax checking mechanism, as shown in the following example:

[0061] <instruction> ::= "RETRIEVE" <attribute list> "WHERE" <condition expression>

[0062] <attribute list> ::= <attribute> {, <attribute>}

[0063] <attribute> ::= "." <field name>

[0064] <conditional expression>::=<simple condition>{<logical operator><simple condition>}

[0065] <simple condition>::=<attribute><comparison operator><value>

[0066] The syntax checking mechanism comprises:

[0067] Missing RETRIEVE or WHERE keyword → trigger error "ERR100: Incomplete instruction structure";

[0068] Attribute without table prefix → trigger error "ERR101: Attribute needs to specify table source".

[0069] (2) Attribute exact mapping, realizing dynamic table field matching, and establishing ambiguity resolution strategy, as shown in the following example:

[0070] P.Name Query P table for Name field Map succeeds (P, Name) StopNum Detect prefix without table name Trigger error ERR102: attribute without table name specified RunTimes.TimeX RunTimes table has no TimeX field Trigger error ERR103: field does not exist in specified table

[0071] if there is a same name attribute (such as "Name" in P / Q):

[0072] Return candidate list: ["1) P.Name", "2) Q.Name"]

[0073] Wait for user selection → record mapping relationship to semantic cache pool.

[0074] (3) Conditional expression structure, used for generating associated logic tree.

[0075] (4) Target table set generation, realizing table dependency analysis and path reachability pre-detection, as shown in the following example:

[0076] {P, RunTimes} Need to connect indirectly through results table Warning "WARN201: need to add results table {Q, Stops} Direct connection Passes validation

[0077] The specific format of the query instruction is Retrieve X Where Y, wherein X is the target attribute, and Y is the condition applied thereto. To translate the query instruction into the SQL command, the JOIN operation needs to be performed on the related table.

[0078] Taking the database shown in Figure 1 as an example, if a user wants to view the protection device with the number of protection actions less than 5 times, the URV query instruction will be:

[0079] RETRIEVE Protection equipment.Name,RunTime.Time,Trip number.TripNum

[0080] WHERE Trip number.TripNum<5

[0081] The translated SQL command will be:

[0082] SELECT Protection equipment.Name, RunTime.Time, Trip number.TripNum

[0083] FROM

[0084] Protection equipment

[0085] JOIN RunTime

[0086] JOIN Protection equipment

[0087] JOIN Trip number

[0088] ON Trip number.TripNum < 5.

[0089] Step S200: Relation graph construction and path optimization

[0090] Step S200 includes the following sub-steps:

[0091] S210: Construct a table relation weighted graph based on the database schema, where the nodes are database tables, and the node weights are the row values of the corresponding tables;

[0092] S220: Calculate the optimal connection sequence using an improved path selection algorithm, and dynamically maintain the minimum row number P for each node min , the connection cost of the adjacency table is calculated as the smaller value of the current P min and the row number of the adjacency table, i.e., the P min of the adjacency table corresponding to the P min of its previous node min The P of the starting node is the smaller value of infinity and the row number of the starting node.

[0093] S230: For queries that span multiple target tables, generate the optimal path of the Steiner tree structure through the global model, and the functional expression of the global cost is as follows:

[0094] Total path cost = Σ single-step connection cost + path minimum row number product

[0095] The goal of path optimization is to minimize the total path cost.

[0096] Relation graph construction and path optimization, based on the structured semantic model output by step S100, generate the optimal table connection sequence, mainly including the following implementation steps:

[0097] (1) Weighted graph dynamic construction, including:

[0098] Node initialization: create a node for each table in the target table set, and design the node weight = real-time table row number;

[0099] The code example of node weight = real-time table row number is as follows:

[0100] weight(Table i ) = SELECT COUNT(*) FROM Table i / / Real-time query;

[0101] Edge connection rule: only when there is a primary-foreign key constraint between two tables, an edge is built, and the edge weight = primary table row number / slave table row number (quantitative connection selectivity);

[0102] The code example of edge weight = primary table row number / slave table row number is as follows:

[0103] weight(Edge_{A→B}) = \frac{\text{row\_count}(A)}{\text{row\_count}(B)}.

[0104] The edge weight can be used to estimate the size of the result set after the connection of two tables. The smaller the ratio, the more efficient the connection, and the larger the ratio may produce a large result set, resulting in reduced efficiency. This value is used to calculate the cost of the connection operation. When the cost is low, this algorithm can also be given priority.

[0105] (2) Improved path selection algorithm, including:

[0106] 2.1) Adjust the dynamic calculation model, define the path minimum row number P min (initial value ∞), and the single-step connection cost as follows:

[0107] Cost_step = min(P min , row_count(Table next ))

[0108] P min update rule:

[0109] P min = min(P min , row_count(Table next ))

[0110] Wherein, Cost_step represents the single-step connection cost, and row_count(Table next ) represents the adjacent table row number.

[0111] 2.2) Steiner tree optimization with multi-objective tables, the global cost function is defined as follows:

[0112] Total_Cost=(ΣCost_step)+(ΠP min )

[0113] Where Total_Cost refers to the total path cost, ΠP min This represents the product of the minimum number of rows in the path corresponding to each node.

[0114] In relational graph construction and path optimization, the most important aspect is improving the path selection algorithm:

[0115] In a Universal Relational View (URV) system, all query operations are performed on a universal set U. To achieve cross-table attribute relationships, a path lookup mechanism is required. Given that database schemas vary depending on the application scenario and implementation method, some schema structures may be quite complex, resulting in multiple potential paths between two tables.

[0116] by Figure 2 Taking the database schema diagram shown as an example, there are multiple feasible paths from table A to table G (such as ACG, ADG, AEG, AFHIG, and ABHIG). Longer paths significantly increase time and resource consumption, a problem that is more pronounced in systems with frequent queries or large, complex schemas. This highlights the importance of efficient pathfinding algorithms.

[0117] Suppose we need to find the shortest path between A and G. To determine the optimal path:

[0118] Introducing such Figure 3 The weighted graph model shown is Figure 3 The value in the upper left corner of each table indicates its row number;

[0119] Define the path weight calculation rules, and multiply the minimum number of rows in the current table and the predecessor table and add them to the total weight. This total value determines the path length.

[0120] Create a list of tables to track which tables have been visited, and mark all of them as unvisited;

[0121] Specify infinity for the initial table;

[0122] Assign a set of weight values ​​to each table, which are represented by two integers P and T, where P represents the minimum number of rows in the path history of the previous table, Pmin (initially infinity), and T represents the number of rows in the current table.

[0123] For the current table, examine all unvisited adjacent items and calculate their provisional distances using the current table.

[0124] For each adjacent item, compare the P value of the current table with the T value of the adjacent table, and assign the smaller value to the P value of the adjacent item.

[0125] The distance is calculated by the following formula:

[0126] D = P C * T n

[0127] P C represents the current P value, T n represents the adjacent table T value;

[0128] Compare the newly calculated tentative distance with the currently assigned value, and assign the smaller value;

[0129] Once all unvisited adjacent items have been considered, mark the current table as visited;

[0130] If the next table has been marked as visited, stop; otherwise, select the unvisited table with the smallest distance and repeat from the step of "assigning a set of weight value parameters to each table".

[0131] The above path finding algorithm flow is as follows:

[0132] [1] Initialize the unvisited table set and create a list of access state markers.

[0133] [2] Set the distance value of the starting table to 0, and the distance values of the remaining tables to infinity.

[0134] [3] Assign weight parameters (P, T) to each table:

[0135] [4] Traverse the unvisited adjacent tables of the current table:

[0136] a) Compare the P value of the current table with the T value of the adjacent table, and assign the smaller value as the P value of the adjacent table;

[0137] b) Calculate the tentative distance according to the formula:

[0138] D = P C * T n

[0139] c) Update the distance value of the adjacent table to the smaller of the current value and the tentative distance;

[0140] [5] Mark the current table as visited.

[0141] [6] If the target table has been visited, terminate; otherwise, select the unvisited table with the smallest distance and repeat steps [3]-[5].

[0142] The design basis of this algorithm is that in the join operation, the "left table" (the connected table) may have participated in the previous join.

[0143] Calculations have verified that ABHJG is the shortest path.

[0144] For multi-table join scenarios (such as joining tables A, G, and H simultaneously), the solution is to construct a shortest distance pairing table (see Table 1) and use a global function. The corresponding path ABHJG in Table 1 is the optimal solution, where H is the common association table. It should be noted that this method uses a cross-join strategy, and can be generalized by adjusting the distance formula (to adapt the time complexity of the corresponding join method) for different join types.

[0145] Table 1

[0146]

[0147] The time complexity of the algorithm is O(m!·n). 2 +m)

[0148] Where m is the number of target tables and n is the total number of tables. Given that the value of m is usually small (the number of target tables in actual queries is often in the single digits), this complexity is within an acceptable range.

[0149] Step S300: Query statement generation and execution

[0150] The optimized table join sequence is converted into standard SQL statements and submitted to the underlying database engine for execution.

[0151] The query statement generation and execution process includes: (1) JOIN chain construction; (2) conditional expression injection; (3) cross-database syntax adaptation, etc., as follows:

[0152] (1) JOIN chain construction;

[0153] (Code Example)

[0154] FROM P

[0155] JOIN results ON PP-Id = results.P-Id / / Automatically deduce the join key

[0156] JOIN drivers ON QR-Id=RR-Id.

[0157] (2) Conditional expression injection, including logic tree to SQL conversion and the establishment of a null value safety mechanism to avoid NULL values ​​causing missing results;

[0158] (Code Example)

[0159] WHERE clause: Injecting user condition WHERE P.stop_num<5

[0160] SELECT clause: project target attributes SELECT P.name, Q.lap_time

[0161] Null safety mechanism: automatically add NOT NULL constraint AND P.P-Id IS NOT NULL.

[0162] (3) Cross-database syntax adaptation, establish language conversion rule library and performance optimization instructions;

[0163] (Code example)

[0164] / *PostgreSQL enhancement* /

[0165] EXPLAIN(ANALYZE,BUFFERS)--execution plan analysis

[0166] SELECT...

[0167] Solution two:

[0168] Under a general inventive concept, the present solution provides a database query optimization system based on a general relational view, for implementing the method of solution one.

[0169] The system comprises the following components:

[0170] A user interface module for providing a natural language instruction input interface;

[0171] A semantic analysis engine, performing step S100, for mapping attributes to database tables;

[0172] A dynamic optimizer, performing step S200, for generating a weighted graph in real time and calculating an optimal path;

[0173] A SQL converter, performing step S300, for generating a syntax-compatible query statement.

[0174] The following will be more detailed in combination with example examples.

[0175] (1) User interface module, this component serves as the interactive entrance of the user and the system, including:

[0176] The first component, instruction input area, is used to provide a structured input form, including: RETRIEVE field: receiving target attribute list (such as P. Name, P. Time) and WHERE field: receiving condition expression (such as P. StopNum < 5), and being limited according to input rules, the input rules including: the attribute needs to be prefixed with table name (table name. attribute), the system automatically checks the legality of the prefix, and whether the condition expression supports comparison operators ( =, >, <) and logical operators (AND, OR) and the like.

[0177] The second component, virtual view generator, is used to dynamically render a universal relationship view (URV), including displaying all table fields according to attribute names and marking the primary-foreign key relationship (such as P. dID→Q. dID), and simultaneously providing interactive functions, including automatically filling the attribute into the RETRIEVE / WHERE field by clicking the attribute and displaying the field description (such as data type, constraint) by hovering.

[0178] The third component, result display panel, is used to output the query result in the form of a two-dimensional table, and can realize differentiated display, including highlighting the associated fields (such as P. Name and Q. Name with the same value marked in blue) and marking the condition filtering items in red border (such as P. StopNum < 5 meeting items).

[0179] The fourth component, error feedback unit, is used to detect and prompt in real time: invalid attribute name (such as not prefixed with table name or spelling error); condition expression syntax error (such as missing operator); path unreachable warning (such as no connection path between target attributes) and the like.

[0180] (2) Semantic analysis engine, the component is responsible for converting user instructions into a machine-operable semantic model, including the following core units:

[0181] The first unit, attribute-table mapping unit, is used to establish a dynamic attribute dictionary and an ambiguity resolution mechanism:

[0182] The dynamic attribute dictionary is established, taking code as an example:

[0183]

[0184] The ambiguity resolution mechanism is established, including automatically adding table name prefix to the same name attribute (such as Name→P. Name) and triggering user selection when there is a conflict (such as popping up "please specify: 1) P. Name 2) Q. Name").

[0185] The second unit, condition expression parse tree generator, is used to convert the WHERE clause into a logical tree structure, such as Figure 4 , while supporting nested logic (such as (Time > 3.0 AND StopNum < 5) OR Name = '801').

[0186] Third unit, table connection relationship verifier, to verify the feasibility based on database schema diagram, including checking whether there is a primary foreign key path between target tables (such as P→pitStops through P-Id connection); when unreachable, trigger error feedback unit (such as "P has no direct association with RunTimes, and needs to be connected through the results table").

[0187] Fourth unit, semantic cache pool, for storing recent analysis results, and supporting self-learning mechanism, including automatic updating of cache after user correction (such as correcting Time to RunTimes.Time); high-frequency access attribute priority recommendation (such as P.Name frequency in database > P.Location).

[0188] (3) Dynamic optimizer, which is the core technical component of the application, used to convert semantic analysis results into optimal table connection sequence through adaptive path selection algorithm driven by table row number, specifically including:

[0189] First unit, weighted graph construction unit, for realizing node definition and establishing edge connection rules:

[0190] Node definition: each database table is regarded as an independent node, and the node weight is the real-time table row number: weight(Tablei)=row_count(Table i );

[0191] Edge connection rule: only when there is a primary-foreign key relationship between two tables, a connection edge is established (such as P.P-Id→Q.P-Id); the edge weight is initialized as the ratio of the associated field cardinality: weight(Edge)=|PK_table| / |FK_table|(such as P(840 rows)→Q(23,657 rows) edge weight=840 / 23657≈0.035).

[0192] Second unit, path selection engine, for adjusting dynamic calculation model:

[0193] Cost_step=min(P min ,row_count(Table next ))

[0194] Where:

[0195] P min : current path history minimum table row number (initial ∞)

[0196] Table next : adjacent table to be connected

[0197] Calculation example:

[0198] Current P min = 200 -> Connect RunTimes (420, 369 rows)

[0199] Cost_step = min(200, 420 369) = 200

[0200] Update rule:

[0201] P min = min(P min , row_count(Table next )) -> P min Keep 200

[0202] The third unit, real-time performance monitor, is used to dynamically adjust the calculation strategy, and a prediction model is established based on historical query time consumption:

[0203] T_predicted = 0.02 x ΣCost_step + 1.5 x 10 -6 x (ΠP min )

[0204] In the above model, the coefficients α = 0.02 and β = 1.5 x 10 -6 are not fixed values, but the core embodiment of the dynamic optimizer to achieve "dynamic adjustment of calculation strategy". Based on the historical query data of the system running continuously, the linear regression method in machine learning is used to fit.

[0205] The acquisition method is as follows:

[0206] The monitor will collect a large amount of query execution data, including: the actual total time consumption (T_actual) of each query plan; the ΣCost_step value corresponding to the plan; the ΠP min value corresponding to the plan.

[0207] Through regression analysis, the optimal coefficients α and β are found, so that the calculation result of the formula α x ΣCost_step + β x (ΠP min ) is closest to the real T_actual value. 0.02 and 1.5 x 10 -6 in the document are the regression coefficients obtained in the current training period.

[0208] The role of setting coefficients α and β is to map the theoretical cost estimate value to the real time (milliseconds or seconds) close to the real time, and balance the two calculations. The prediction model will be retrained regularly or triggered. When the system runs for a period of time and accumulates new historical data, the monitor can start a new round of regression fitting to generate a new set of coefficients (α, β).

[0209] This means that the prediction of the model will be adjusted dynamically and evolved according to the actual running status of the database (such as changes in hardware performance, data growth, and load feature migration), so as to maintain the prediction accuracy.

[0210] The fourth unit is an output verification interface, which generates an optimal path report based on the predicted minimum time cost.

[0211] (4) SQL converter, which is used to convert the path sequence generated by the dynamic optimizer into an executable SQL statement, and includes the following core technical components:

[0212] The first unit is a JOIN chain construction engine, which establishes path sequence mapping rules and implements connection type selection;

[0213] The second unit is a condition expression injector, which accurately converts the logical tree of the semantic analysis engine into SQL conditions, as shown in Figure 5 ;

[0214] The third unit is a syntax adapter, which realizes cross-database dialect conversion.

[0215] The core technology of the present scheme will be described below in conjunction with embodiment 1 of a specific application scenario.

[0216] Embodiment 1: Relay protection device operation library

[0217] [1] Through user analysis instructions:

[0218] RETRIEVE relay protection device. Device model, event record. Event type, manufacturer. Primary / secondary protection device

[0219] WHERE manufacturer. Country of origin = 'Germany'

[0220] [2] Semantic analysis engine processing:

[0221] Attribute mapping is:

[0222]

[0223] Condition analysis is:

[0224] WHERE manufacturer. Country of origin = 'Germany'

[0225] [3] Dynamic optimizer flow:

[0226] As shown in Figure 6 , the tables associated with the target attributes and conditions in the query instruction include the event record table (100 rows), the action log table (20 rows), the relay protection device table (200 rows), and the manufacturer table (50) four target tables.

[0227] The traditional path of the inquiry is "event record -> relay protection device -> manufacturer", and in the path optimized based on the Steiner tree, a transit station, i.e. the action log table (20 rows), is added.

[0228] 1) According to the traditional path (event record -> relay protection device -> manufacturer), the path steps and cost calculation are as follows: connecting the event record table (100) -> the relay protection device table (200)

[0229] Cost_step1 = min(∞, 200) = 200

[0230] P min = min(∞, 200) = 200 connecting the relay protection device table (200) -> the manufacturer table (50)

[0231] Cost_step2 = min(200, 50) = 50

[0232] P min = min(200, 50) = 50

[0233] ∑Cost_step = 200 + 50 = 250

[0234] ∏P min = 200 x 50 = 10,000 (path Pmin product)

[0235] Original total cost = 250 + 10,000 = 10,250

[0236] 2) Optimize path cost calculation (event record -> action log table -> relay protection device -> manufacturer)

[0237] Path steps and cost calculation:

[0238] Connecting the event record table (100) -> the action log table (20)

[0239] Cost_step1 = min(∞, 20) = 20

[0240] P min = min(∞, 20) = 20

[0241] Connecting the action log table (20) -> the relay protection device table (200)

[0242] Cost_step2 = min(20, 200) = 20

[0243] P min = min(20, 200) = 20 connecting the relay protection device table (200) -> the manufacturer table (50)

[0244] Cost_step3 = min(20, 50) = 20

[0245] P min =min(20,50)=20

[0246] ΣCost_step=20+20+20=60

[0247] ∏P min =20×20×20=8,000(path P) min product)

[0248] Total cost = 60 + 8,000 = 8,060

[0249] That is, by relaying data through the action log table (20 lines), Max(P) can be effectively controlled. min )value.

[0250] In summary, based on the background description, regarding the complexity of user operations, in traditional query solutions, non-technical personnel need to master SQL syntax and database structure to perform effective queries. While existing simplified solutions using URV language lower the input threshold, they do not optimize multi-table join paths, resulting in still low efficiency for complex queries. Regarding the performance bottleneck of multi-table joins, when traditional path algorithms are directly applied to database queries, they are prone to defects in the weighting mechanism (failing to consider the decisive impact of the number of table rows on the time complexity of join operations) and a lack of global optimization (using independent path aggregation when joining multiple target tables, without establishing a model to minimize global computational cost).

[0251] Compared with the prior art, the specific embodiments provided by the present invention have many advantages, including:

[0252] 1) In terms of ease of use, a "RETRIEVE-WHERE" type natural language interface is adopted, which completely eliminates the need for users to write JOIN statements and solves the query obstacles for non-technical personnel;

[0253] 2) In terms of query efficiency, through the improved path optimization algorithm (dynamic cost calculation based on the number of table rows and global optimization of tree structure), the number of single execution operations of complex queries is only slightly increased compared to native SQL. According to an implementation test case, the increase is about 0.0077%, which significantly improves query efficiency.

[0254] 3) In terms of result accuracy, the result set can be guaranteed to have no redundant NULL values ​​through fully connected views and projection NULL removal mechanisms;

[0255] 4) In the compatibility level, the middleware supports standard SQL databases such as MySQL / PostgreSQL, and can generate syntax-compatible JOIN sequences through a path optimization algorithm.

[0256] The exemplary embodiments of the present application are illustrated in the accompanying drawings, while it is to be understood that the present application is not limited to the embodiments set forth herein. Rather, the embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the application to those skilled in the relevant art.

Claims

1. A database query optimization method based on a general relational view, characterized in that, Includes the following steps: The query command is parsed, automatically identifying the database tables associated with the target attributes and conditions contained in the query command, and obtaining the target table set; A weighted graph is constructed with database tables as nodes and node weights equal to the real-time row count of the database tables. For multi-table queries targeting a set of tables, the optimal path is selected based on the lowest global cost. The global cost consists of the sum of the single-step connection costs of each node in the path and the minimum number of rows P required to reach each node. min Let the product of the terms be the single-step join cost, which is P, the minimum number of rows in the path between the current node and the current adjacency list. min The smaller of the values, the minimum number of rows P in the path to the current node. min P is the real-time row count of the current node and the minimum row count of the path to the previous node. min The smaller of the values, the minimum number of rows P in the path from the starting node. min Take the smaller value between infinity and the real-time row count of the starting node of the path; Optimize the table join sequence according to the selected optimal path, generate standard database query SQL statements based on the optimized table join sequence, and execute them.

2. The database query optimization method based on a general relational view as described in claim 1, characterized in that, The query command parsing process includes a syntax check, and the check mechanism includes checking the integrity of the query command structure and verifying the validity of the attribute table name prefix.

3. The database query optimization method based on a general relational view as described in claim 1, characterized in that, Query command parsing includes the following sub-steps: The target attributes are mapped to fields in each database table using a dynamic dictionary, and a mechanism for resolving ambiguity of attributes with the same name is constructed. Convert the WHERE condition expression in the query command into a logic tree structure; Verify the reachability between tables based on primary and foreign key constraints.

4. The database query optimization method based on a general relational view as described in claim 3, characterized in that, The generation and execution of standard database query statements includes the following sub-steps: Automatically deduce the primary and foreign key conditions of each table based on the optimized table join sequence, and construct the JOIN chain; Convert the logic tree into an SQL WHERE clause and add null-safe constraints; Adapt SQL dialects for different databases using a language conversion rule base.

5. A database query optimization method based on a general relational view as described in claim 3 or 4, characterized in that, The logic tree structure is a Steiner tree structure.

6. A database query optimization system based on a general relational view, characterized in that, include: The user interface module is used to provide a query command input interface and receive query commands. The semantic parsing engine is used to parse the target attributes and conditions in the query command, automatically identify the database tables associated with the target attributes and conditions, and generate a set of target tables; A dynamic optimizer constructs a weighted graph with database tables as nodes and node weights equal to the real-time row count of the database tables. For multi-table queries targeting a set of tables, it selects the optimal path based on the lowest global cost. This global cost comprises the sum of the single-step join costs of each node in the path and the minimum number of rows P required to reach each node. min Let the product of the terms be the single-step join cost, which is P, the minimum number of rows in the path between the current node and the current adjacency list. min The smaller of the values, the minimum number of rows P in the path to the current node. min P is the real-time row count of the current node and the minimum row count of the path to the previous node. min The smaller of the values, the minimum number of rows P in the path from the starting node. min Take the smaller value between infinity and the real-time row count of the starting node of the path; The SQL converter optimizes the table join sequence according to the optimal path, generates standard database query statements based on the optimized table join sequence, and submits them to the underlying database engine for execution.

7. A database query optimization system based on a general relational view as described in claim 6, characterized in that, The user interface module includes the following components: The instruction input area is used to provide a structured query instruction input form; A virtual view generator is used to dynamically render a general relationship view, which includes all table fields categorized by attribute name and tags primary and foreign key relationships; The results display panel is used to output the query results.

8. A database query optimization system based on a general relational view as described in claim 6, characterized in that, The semantic parsing engine includes the following units: The attribute-table mapping unit is used to map target attributes to fields in various database tables through a dynamic dictionary, and to build a mechanism to resolve ambiguities of attributes with the same name. A conditional expression parse tree generator is used to convert the WHERE conditional expression in a query instruction into a logical tree structure. A table join validator used to verify reachability between tables based on primary and foreign key constraints.

9. A database query optimization system based on a general relational view as described in claim 6, characterized in that, The dynamic optimizer includes the following units: The weighted graph construction unit is used to construct a weighted graph with database tables as nodes and node weights equal to the real-time number of rows in the database table. A path selection engine is used to adjust a dynamic computation model, which includes calculating the single-step connection cost for each node and the minimum number of path rows P corresponding to each node. min Calculation; A real-time performance monitor is used to adjust the global cost calculation strategy. The global cost consists of the sum of the single-step connection costs of each node in the path and the minimum number of rows P required to reach each node. min The calculation strategy for the multiplication term is adjusted by adding a coefficient that is multiplied by the summation term and the multiplication term; Output verification interface, generate optimal path report based on minimum global cost.

10. A database query optimization system based on a general relational view as described in claim 8, characterized in that, The SQL converter includes: The JOIN chain construction engine establishes path sequence mapping rules and implements connection type selection. The conditional expression injector accurately converts the logic tree of the semantic parsing engine into SQL statement conditions; A syntax adapter, based on a language conversion rule base, adapts to different database SQL dialects, enabling cross-database dialect conversion.

Images