SQL (Structured Query Language) multi-table associated query performance optimization method based on syntax tree reconstruction and readable medium

Abstract

The invention discloses an SQL multi-table associated query performance optimization method based on syntax tree reconstruction and a readable medium, and relates to the technical field of database query optimizing.The SQL multi-table associated query performance optimization method comprises the steps that SQL multi-table associated query statements are obtained, and a syntax tree is constructed; a core node of the syntax tree comprises a SelectItem list, a FromItem object, a Join object linked list, an ExpressiOn object and a DistInct keyword; carrying out meta-information extraction based on the hierarchical syntax tree; splitting and reconstructing the SQL multi-table associated query into single-table query based on the syntax tree meta-information, and generating an atomic execution plan; the atomic execution plan comprises SQL query statements of a main table and a sub-table; the database is connected and executed according to the SQL query statements of the main table and the sub-table, and a final SQL multi-table associated query result is generated, the stability of a database system is improved, the execution efficiency is high, and the resource utilization rate is improved.

Description

Technical Field

[0001] This invention relates to the field of database query optimization technology, and in particular to a method for optimizing the performance of SQL multi-table join queries based on syntax tree reconstruction and a readable medium. Background Technology

[0002] In the context of massive data processing in the digital age, multi-table join queries are one of the most common operations in database systems. Traditional multi-table join queries suffer from the following technical problems: 1. Low execution efficiency: As the number of joined tables increases, the complexity of the execution plan grows exponentially, leading to a sharp increase in query response time; 2. High memory consumption: The Cartesian product-style result set assembly method results in uncontrollable memory usage, which can easily lead to out-of-memory errors and system crashes in scenarios with tens of millions of data points; 3. Limited concurrency: Complex join queries occupy database connection resources for a long time, reducing the overall throughput of the system. Therefore, optimizing the performance of SQL (Structured Query Language) multi-table join queries is crucial.

[0003] Existing solutions, such as retrieval optimization techniques, accelerate the data matching process between tables by creating appropriate data structures (B-Tree, HAsh, Bitmap) on related fields. However, index maintenance costs are high, write operations require synchronous index updates, and memory consumption is large. Materialized view technology is a database object that persistently stores pre-computed result sets. It is essentially a space-for-time optimization strategy, requiring additional storage space to store pre-computed results. Furthermore, the real-time performance of the data is limited by the refresh mechanism and cannot reflect the latest changes in the base table. Summary of the Invention

[0004] To overcome the shortcomings of low execution efficiency, excessive memory consumption, and insufficient concurrency in multi-table join queries in the existing technologies, this invention proposes a performance optimization method for SQL multi-table join queries based on syntax tree reconstruction and a readable medium.

[0005] To achieve the above objectives, the present invention adopts the following technical solution: a performance optimization method for SQL multi-table join queries based on syntax tree reconstruction, characterized in that it includes:

[0006] S1: Obtain the SQL multi-table join query statement and construct the syntax tree; the core nodes of the syntax tree include the SelectItem list, FromItem object, Join object linked list, ExpressIn object, and DistInct keyword;

[0007] S2: Meta-information extraction based on syntax tree;

[0008] S3: Based on the syntax tree metadata, SQL multi-table join queries are split and reconstructed into single-table queries, and atomic execution plans are generated; the atomic execution plan includes the SQL query statements for the main table and the sub-table;

[0009] S4: Connect to the database based on the SQL query statements of the main table and the sub-table, execute them, and assemble them in memory to obtain the query results of multiple tables.

[0010] Preferably, in step S1, the SQL multi-table join query statement is obtained, and a syntax tree is constructed, including:

[0011] S11: Obtain the SQL multi-table join query statement and clean it to obtain the original SQL multi-table join query statement;

[0012] S12: The JSQLParser parsing tool is used to generate a token stream from the original SQL multi-table join query statement. The token stream is a series of meaningful syntactic units decomposed according to the syntax rules.

[0013] S13: Construct a syntax tree using a token stream;

[0014] The semantics of the core nodes in the syntax tree are as follows:

[0015] The SelectItem list stores the column expressions selected by the Select property.

[0016] The FromItem object records the main table information of the From clause, which includes the regular table name and the subquery expression.

[0017] The Join object linked list stores Join conditions and sub-table information;

[0018] The ExpressiOn object stores the Where condition.

[0019] Preferably, S2: Meta-information extraction based on the normal tree, including:

[0020] S21: Perform table relationship identification;

[0021] S22: Based on the SelectItem list and the table's alias information, query the column objects and construct the column mapping relationship. The column objects include the table name, column name, and column alias. The mapping relationship format is: table name.column name→column alias;

[0022] S23: Extract Join conditions: Search each Join clause expression based on the Join object linked list and obtain the associated fields after the On keyword;

[0023] S24: Extracting Distinct keywords based on hierarchical syntax trees;

[0024] S21: Perform table relationship identification, including:

[0025] S211: Obtain main table information based on FromItem object, identify the type of main table, and determine whether the main table is a regular table or a subquery;

[0026] S212: Analyze all Join relationships and identify sub-tables based on the Join object linked list. Sub-tables are the tables between the Join keyword and the On keyword.

[0027] S213: Stores alias information for the main table and child tables.

[0028] Preferably, S3: Based on the syntax tree meta-information, the SQL multi-table join query is split and reconstructed into a single-table query, and an atomic execution plan is generated, including:

[0029] S31: Perform main table identification: Identify the main table in the From clause and use it as the base table of the atomic execution plan. Select the first non-subquery in the From clause as the main table; if the main table is a subquery, directly use the subquery expression as the main table object.

[0030] S32: Construct a topological sorting structure based on a linked list of Join objects, including:

[0031] S321: Identify the Join type, which includes Left Join, Cross Join, and Inner Join.

[0032] S322: Construct inter-table dependencies based on information from the main table and child tables, join conditions, and join types, and generate a topological sorting structure;

[0033] S33: Based on the topological sorting structure and the Where condition, push down the Where condition expression to construct the mapping relationship between the Where condition expression and the table;

[0034] S34: Generate atomic execution plans based on the mapping relationship between topological sorting structure, conditional expressions, and tables.

[0035] Preferably, S34: Generate an atomic execution plan based on the mapping relationship between the topological sorting structure and the conditional expression and the table, including:

[0036] S341: Construct the main table SQL query statement based on the identified main table, column mapping relationship, and WHERE condition expression mapping relationship with the table;

[0037] S342: Convert the Join condition into an In query and construct it into the Where condition of the sub-table to obtain the SQL query statement of the sub-table.

[0038] Preferably, S4: Connect to the database based on the SQL query statements of the main table and the sub-table, execute and assemble them in memory, and obtain the multi-table query results, including:

[0039] S41: Obtain the main table parameters input by the user; perform parameter count verification; if the number of parameter counts exceeds the preset parameter count threshold N, issue an alarm and require manual intervention;

[0040] S42: Bind the user-input parameters to the main table SQL query statement, connect to the database to execute the main table SQL query statement, and obtain the main table SQL query results;

[0041] S43: Extract the values ​​of the related fields from all sub-table SQL query statements, including:

[0042] S44: Perform parallel queries on all sub-tables and obtain the SQL query results for all sub-tables;

[0043] S45: Merge the SQL query results of all tables to generate the final SQL multi-table join query result.

[0044] Preferably, the feature is that, S43: extracting the values ​​of the related fields from the SQL query statements of all sub-tables, including:

[0045] S431: Parse and extract the IN field of all sub-table SQL query statements;

[0046] S432: Traverse the SQL query results of the main table, mark the values ​​of the fields associated with the child table, and filter out null values.

[0047] Preferably, the feature is that, S44: performing parallel queries on all sub-tables to obtain the SQL query results of all sub-tables, including:

[0048] S441: Look up database metadata based on sub-table name, obtain statistical information for all sub-tables, estimate the size of the result set for each sub-table based on the statistical information and conditional expression for each sub-table, and pre-allocate memory space for the result set of each sub-table; the statistical information includes the total number of rows in the table, the index cardinality, and the data page size;

[0049] S442: Bind the value of the associated field as the key value of the main table to the IN parameter of the SQL query of the child table;

[0050] S443: Use a thread pool to concurrently execute SQL query statements for multiple sub-tables and obtain the SQL query results for all sub-tables; if the execution time exceeds the time consumption threshold, throw a timeout exception and record it; continue to retry; if multiple queries fail, issue an alarm and require manual intervention.

[0051] S444: Grouping sub-table SQL query results using composite keys with multiple fields;

[0052] S445: Load the SQL query results of the grouped sub-tables in batches.

[0053] Preferably, S45: Join and merge the SQL query results of all tables to generate the final SQL multi-table join query result, including:

[0054] S451: Based on the topological sorting structure, the composite key of each sub-table, and the SQL query results of each sub-table, obtain the matching data of each sub-table;

[0055] S452: Based on the SQL query results of the main table and the matching data of each sub-table, perform column relationship mapping and merge;

[0056] S453: Based on the Join type, merges data from all tables to generate the final SQL multi-table join query result.

[0057] A readable medium includes a memory and a processor, wherein a computer program is stored in the memory, and the processor is connected to the memory and is used to execute the computer program to implement a SQL multi-table join query performance optimization method based on syntax tree reconstruction.

[0058] The advantages of this invention are:

[0059] (1) This invention obtains SQL multi-table join query statements, constructs a syntax tree, extracts meta-information, splits and reconstructs SQL multi-table join queries into single-table queries, and generates atomic execution plans; connects and executes the database based on the SQL query statements of the main table and sub-tables, and generates the final SQL multi-table join query results. The query performance is significantly improved, the system throughput is increased, the stability is enhanced, the resource utilization is improved, and it supports the processing of tens of millions of data.

[0060] (2) This invention effectively reduces the computational complexity of multi-table joins and reduces the optimization burden of the database engine by splitting complex multi-table queries into single-table atomic queries and executing them in parallel; it uses IN queries instead of Join operations to avoid the Cartesian product explosion problem, which is especially suitable for large table join scenarios; it uses conditional expressions based on topological sorting to filter invalid data in advance and reduce the size of intermediate result sets.

[0061] (3) This invention prevents memory overflow by pre-allocating memory based on statistical information, improves system stability, reduces single memory consumption by batch loading mechanism, is suitable for massive data processing, and makes full use of multi-core CPU resources by concurrent execution of thread pool, thus shortening the overall response time. Attached Figure Description

[0062] Figure 1 This is a flowchart of the method of the present invention;

[0063] Figure 2 A flowchart of syntax tree analysis based on JSQLParser;

[0064] Figure 3 This is a flowchart of the memory assembly process. Detailed Implementation

[0065] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0066] Example 1

[0067] like Figure 1 As shown, this invention proposes a performance optimization method for SQL multi-table join queries based on syntax tree reconstruction, including:

[0068] S1: Obtain the SQL multi-table join query statement, construct a syntax tree, including:

[0069] S11: Obtain the SQL multi-table join query statement and clean it to obtain the original SQL multi-table join query statement; the cleaning includes removing special characters such as comments and newline characters;

[0070] S12: The built-in syntax of the JSQLParser parsing tool is used to generate a token stream from the original SQL multi-table join query statement. The token stream is a series of meaningful syntactic units decomposed according to the syntax rules.

[0071] For example, for a raw SQL multi-table join query statement

[0072] Select a.id, b.name

[0073] From table_aa

[0074] Join table_bb on a.id = b.pid

[0075] Where a.status=1

[0076] The generated token stream contains syntax units such as Select, a.id, b.name, From, table_a, Join, table_b, On, and a.id = b.pid.

[0077] S13: Construct a hierarchical syntax tree (AsT) using a token stream. The core nodes of the hierarchical syntax tree include:

[0078] SelectItem list: Stores the column expressions after Select (such as a.id, b.name);

[0079] FromItem object: Records the main table information (such as table_aa) of the From clause. The main table information includes the regular table name and the subquery expression;

[0080] Join object linked list: stores Join conditions (e.g., Ona.id = b.pid) and sub-table information (e.g., table_bb);

[0081] The ExpressiOn object stores the Where conditional expression (e.g., a.status = 1);

[0082] The DistInct keyword: Removes duplicate rows from the query results;

[0083] S2: Meta-information extraction based on hierarchical syntax trees, including:

[0084] S21: Perform table relationship identification, including:

[0085] S211: Obtain main table information based on FromItem object, identify the type of main table, and determine whether the main table is a regular table or a subquery;

[0086] S212: Analyze all Join relationships and identify sub-tables based on the Join object linked list. Sub-tables are the tables between the Join keyword and the On keyword.

[0087] S213: Store alias information for the main table and child tables;

[0088] S22: Perform column mapping construction, including:

[0089] S221: Based on the SelectItem list and the table alias information, query the column objects and construct the column mapping relationship. The column objects include the table name, column name, and column alias. The mapping relationship format is: table name.column name→column alias;

[0090] S23: Extract Join conditions: Search each Join clause expression based on the Join object linked list and obtain the associated fields after the On keyword, for example, a.id = b.id;

[0091] S24: Extracting Distinct keywords based on hierarchical syntax trees;

[0092] S3: Based on syntax tree metadata, SQL multi-table join queries are split and reconstructed into single-table queries, and atomic execution plans are generated, including:

[0093] S31: Perform main table identification: Identify the main table in the From clause and use it as the base table of the atomic execution plan. Select the first non-subquery in the From clause as the main table; if the main table is a subquery, directly use the subquery expression as the main table object.

[0094] The following two points should be noted:

[0095] (1) The difference between S211 and S31

[0096] S211: Meta-information extraction stage, used to identify the type of the main table, only records information, does not involve operational decisions, and outputs the main table type label. S31: Decomposition and reconstruction stage, determines the "main table object" as the basis for decomposition, providing a clear starting point (base table) for subsequent generation of atomic execution plans, and outputs an executable main table query object. In short, S211 is "identifying the type of the main table," and S31 is "determining what to do with this main table (as the starting point for decomposition)." The former is information collection, and the latter is operation execution; the two are logically progressive.

[0097] (2) Regardless of whether the From clause contains a regular table or a subquery, it serves as a justification for the use of the "main table".

[0098] The definition of the main table follows SQL syntax: the core object of the From clause (the first non-subquery or subquery) is the basis for multi-table joins. All Join operations are based on this core object, so regardless of its type, it is used as the main table to ensure that the splitting logic is consistent with the original SQL semantics.

[0099] S32: Construct a topological sorting structure based on a linked list of Join objects, including:

[0100] S321: Identify the Join type, which includes Left Join, Cross Join, and Inner Join.

[0101] S322: Based on the information of the main table and child tables, the join conditions, and the join type, construct the inter-table dependencies and generate a topological sorting structure, for example:

[0102] Left Join child table B (join condition A.id = B.pid)

[0103] Main table A → Inner Join child table C (join condition A.id = C.pid)

[0104] Subtable B → Inner Join subtable C (join condition B.id = C.bid)

[0105] The topological sorting structure is A→B→C, which ensures that the table execution order is consistent with the original semantics.

[0106] S33: Based on the topological sorting structure and the Where condition, push down the Where condition expression to build a mapping relationship between the Where condition expression and the table, and accurately match the condition to the corresponding table.

[0107] The "push-down of the Where condition expression" in this invention is a splitting and precise allocation of the original Where condition. The purpose is to advance the filtering conditions that originally applied to the results of multi-table joins to the single-table query stage, thereby reducing the size of the result set of a single-table query.

[0108] For example, suppose the original SQL multi-table join query is:

[0109] Select a.id, b.name

[0110] From table_aa

[0111] Join able_bb on a.id = b.pid

[0112] Where a.status=1And b.type='user'

[0113] In the SQL above, where a.status = 1 and b.type = 'user', the "Where condition expression pushdown" will split the Where condition according to the table it belongs to:

[0114] The WHERE condition expression for the parent table `table_a` is: `a.status = 1` (assigned to the parent table query);

[0115] The Where condition expression for the sub-table table_b is: b.type = 'user' (assigned to the sub-table query).

[0116] S34: Generate atomic execution plans based on the mapping relationship between the topological sorting structure and conditional expressions and tables, including:

[0117] S341: Construct the main table SQL query statement based on the identified main table, column mapping relationship, and WHERE condition expression mapping relationship with the table;

[0118] For example, suppose the original SQL multi-table join query is:

[0119] Select o.Order_id, c.name

[0120] From Orders o -- Main table (the first non-subquery in the From clause)

[0121] Join customers c On o.customer_id=c.customer_id--Join conditions

[0122] Where o.Order_date>'2023-01-01'And c.country='CN'--Where condition

[0123] According to the "Where condition expression pushdown" logic in S33, the original Where condition o.Order_date>'2023-01-01'And c.country='CN' is split into the following based on table affiliation:

[0124] The conditional expression for the main table (Orders o) is: o.Order_date>'2023-01-01' (condition only includes fields from the main table);

[0125] The conditional expression for the sub-table (customers c) is: c.country = 'CN' (condition that only includes fields from the sub-table).

[0126] The function of S341 is to generate an executable SQL query statement for the main table based on the main table, column mapping relationships, and conditional expressions of the main table.

[0127] The main table identified in step S31: Orders o;

[0128] The column mapping relationship in step S221 is: Orders.Order_id → o.Order_id (the o.Order_id in the original SQL Select corresponds to the Order_id column in the Orders table);

[0129] The main table condition expression for step S33 is: o.Order_date>'2023-01-01'.

[0130] Therefore, the SQL query statement for the constructed main table is:

[0131] Select o.Order_id, o.customer_id -- This contains the related field (o.customer_id) needed for the associated sub-table.

[0132] From Orders

[0133] Where o.Order_date>'2023-01-01' -- This applies the conditional expression of the main table in the mapping relationship between the WHERE conditional expression and the table in S33.

[0134] S342: Convert the Join condition into an In query and construct it into the Where condition of the sub-table. For example: a.id = b.pid --> b.pid In(:id), where id is the attribute value of the id column in the main table; at the same time, based on the topological sorting structure, column mapping relationship and the condition expression constructed in step S33, construct an executable sub-table SQL query statement for each Join object.

[0135] Example 1: Select o.Order_id,o.Order_date,c.name As customer_name,c.email

[0136] From Orders

[0137] Inner Join customersc On o.customer_id=c.customer_id;

[0138] Convert to:

[0139] Select o.Order_id,o.Order_date,o.customer_id

[0140] From Orders

[0141] Select c.name, c.email

[0142] From customers c

[0143] Where c.customer_id In(:customer_id)

[0144] Example 2, taking the query case in step 341 as an example.

[0145] Convert the Join condition to an In query: o.customer_id = c.customer_id --> c.customer_id In(:customer_id)

[0146] Topological sorting structure: Orders → customers

[0147] Column mapping relationship: customers.name → c.name

[0148] The conditional expression for the sub-table constructed in step S33 is: c.country = 'CN'

[0149] Therefore, the SQL query statement for the constructed sub-table is:

[0150] Select c.name

[0151] From customers c

[0152] Where c.country='CN'And c.customer_id In(:customer_id)

[0153] S4: Connect to the database based on the SQL query statements of the main table and child tables, execute the query, and assemble the data in memory to obtain the results of the multi-table query, including:

[0154] S41: Obtain the main table parameters input by the user; perform parameter count verification; if the number of parameter counts exceeds the preset parameter count threshold N, issue an alarm and require manual intervention;

[0155] For example, in `a.id In(:params)`, `params` is the set of parameters input by the user; the parameter count threshold N is a configurable value. This limitation mainly targets the filtering parameters of key fields (such as ID) in the main table to avoid the main table query result set becoming too large due to too many parameters (such as inputting 100,000 IDs at once), which would exceed the memory processing capacity.

[0156] S42: Bind the user-input parameters to the main table SQL query statement, connect to the database to execute the main table SQL query statement, and obtain the main table SQL query results;

[0157] When executing SQL queries on the main table in the database, the execution time is checked. If the execution time exceeds the time threshold, a TimeoutException is thrown and recorded. The system continues to retry. If multiple queries fail, an alarm is triggered and manual intervention is required.

[0158] If the query result of the main table is empty, return an empty set directly;

[0159] S43: Extract the values ​​of the related fields from all sub-table SQL query statements, including:

[0160] S431: Parse and extract the IN field of all sub-table SQL query statements;

[0161] S432: Traverse the SQL query results of the main table, mark the values ​​of the fields associated with the child table, and filter null values; if there are multiple field associations (such as a.id and a.type), combine them into a composite key (such as (1,'type1').

[0162] For example, if there are multiple fields associated (such as a.id and a.type), they can be combined into a composite key (such as (1,'type1')).

[0163] S44: Perform parallel queries on all sub-tables and retrieve the SQL query results for all sub-tables, including:

[0164] S441: Look up database metadata based on sub-table name, obtain statistical information for all sub-tables, estimate the size of the result set for each sub-table based on the statistical information and conditional expression for each sub-table, and pre-allocate memory space for the result set of each sub-table; the statistical information includes the total number of rows in the table, the index cardinality (the number of all distinct values ​​of a field), and the data page size.

[0165] Metadata refers to structured information recorded in a database that describes table structure and data characteristics. It is statistical data built into the database system and requires no additional manual maintenance. Specifically, it includes:

[0166] Basic table information: table name, database, storage engine;

[0167] Field information: field name, data type, length, whether it is a primary key / index;

[0168] Statistical data: total number of rows in the table, index cardinality (number of distinct values ​​in a column), data page size, etc.

[0169] Purpose: By obtaining this metadata (such as the total number of rows in the sub-table and the index cardinality of the associated fields), and combining it with the conditional expressions in step S33 (such as the filtering conditions of the sub-table), the size of the result set of the sub-table query can be estimated (for example, if the total number of rows in the sub-table is 10 million and the conditional filtering ratio is about 50%, then the estimated result set is 5 million rows), providing a basis for "pre-allocating memory space" and avoiding performance loss caused by dynamic memory expansion.

[0170] S442: Bind the value of the associated field as the key value of the main table to the IN parameter of the SQL query of the child table;

[0171] The IN parameter in the sub-table SQL query comes from the join key value of the main table, ensuring that the sub-table query only retrieves data that matches the main table. This is a key part of the "single table splitting + join matching" logic.

[0172] S443: Use a thread pool to concurrently execute SQL queries on multiple sub-tables and retrieve the SQL query results for all sub-tables; if the execution time exceeds the time consumption threshold, throw a TimeoutException and record it; continue to retry; if multiple queries fail, issue an alarm and require manual intervention.

[0173] S444: Grouping sub-table SQL query results using composite keys with multiple fields;

[0174] S445: Load the SQL query results of the grouped sub-tables in batches to avoid the full data remaining in memory (M rows per batch by default);

[0175] S45: Join and merge the SQL query results of all tables to generate the final SQL multi-table join query result, including:

[0176] S451: Based on the topological sorting structure, the composite key of each sub-table, and the SQL query results of each sub-table, obtain the matching data of each sub-table;

[0177] S452: Based on the SQL query results of the main table and the matching data of each sub-table, perform column relationship mapping and merge;

[0178] If column conflicts exist, they will be resolved according to the configured conflict policy (supporting OVERWRITE, MERGE, and SKIP).

[0179] S453: Based on the Join type, merges data from all tables to generate the final SQL multi-table join query result.

[0180] Join types include Inner Join (self-join), Left Join (left join), and Cross Join (cross join).

[0181] Inner Join: Discard records if no match is found; Left Join: Retain records from the main table and fill with null if no match is found; Cross Join: Generates a Cartesian product.

[0182] Resource management is implemented in the solution of this invention:

[0183] Based on step 4, a multi-layered defense mechanism is added to ensure the controllability of query size.

[0184] (1) Implement SQL input parameter limit: Limit the number of SQL input parameters (the number of parameters is configurable, default is N). If the number of parameters exceeds the specified number, an alarm will be launched and manual intervention will be required.

[0185] (2) Streaming processing: Batch loading is used to avoid full data residing in memory (M records per batch by default).

[0186] (3) Prediction mechanism: Pre-allocate memory space for result set based on statistical information.

[0187] Connect to database metadata to obtain table statistics (such as total number of rows and index cardinality), calculate the estimated number of rows (add filtering conditions and heuristic rules; the default condition filters 50% of the data, which can be configured), and pre-allocate the collection size based on the estimated number of rows.

[0188] An exception handling mechanism is implemented in the solution of this invention:

[0189] Based on the memory data assembly in step 4, a dual circuit breaker mechanism of internal query and timeout exception is added to ensure service stability.

[0190] (1) Memory circuit breaker: When the assembled data exceeds the threshold, a MemoryLimitException (memory limit exception, configurable, default K records) is thrown, and an alarm is issued to prompt manual intervention.

[0191] Additional notes:

[0192] Data assembly: This refers to the amount of data to be merged when a single sub-table is matched with the main table. Because the amount of data in the main table has been controlled to a relatively small range by the input parameter limits in step S41 (such as the number of ID parameters in the main table), but the amount of data to be merged in the sub-table under a "one-to-many" relationship may be much larger than that in the main table (e.g., 100 records in the main table correspond to 10,000 records in the sub-table), becoming the main risk point for memory consumption. Therefore, a memory circuit breaker is used to control the amount of data to be merged in a single sub-table. By monitoring the amount of data in the sub-table and triggering the circuit breaker, memory overflow can be precisely prevented, complementing the input parameter limits of the main table (step S41) and jointly ensuring system stability.

[0193] (2) Timeout Circuit Breaker: Limits the time taken for a single query assembly (configurable, default T seconds). If the timeout occurs, a TimeoutException is thrown and the timeout task information is recorded in the error table for subsequent retry. If the retry fails after 3 attempts, an alarm is triggered and manual intervention is required.

[0194] The following example, using a multi-table join query on an e-commerce platform, demonstrates the practical application scenarios of the solution and the triggering logic of the circuit breaker mechanism:

[0195] 1. Original multi-table join query (Scenario: Querying a specified list of orders and related information)

[0196] Select o.id,o.Order_id,o.Order_time,u.user_name,u.phOne,

[0197] p.product_name,p.price,oi.quantity,(p.price*oi.quantity)As total_amount From Orders o

[0198] Left Join users u On o.user_id=u.user_id

[0199] Inner Join Order_items oi On o.Order_id=oi.Order_id

[0200] Inner Join products p On oi.product_id=p.product_id

[0201] Where o.id In(:id) / / User input parameter, for example: 1,2,3,4,5,6,7,8,9,10.....

[0202] And u.user_level = 'VIP'

[0203] And p.categOry = '3C'

[0204] OrderBy o.Order_time Desc;

[0205] Note: Left Join returns all records from the left table and the join field from the right table that is equal to the join field. Inner Join returns only rows that meet the query and join conditions.

[0206] Scenario characteristics: It involves 4 tables (Orders is the main table, and users / Order_items / products are the sub-tables) with a huge amount of data (10 million records in the Orders table, 5 million records in the users table, 30 million records in the Order_items table, and 1 million records in the products table). If traditional multi-table joins are performed directly, it is easy to trigger problems such as low efficiency and memory overflow.

[0207] 2. Solution Implementation Process

[0208] (1) Syntax Tree Construction and Meta-information Extraction

[0209] Identify the main table: Orders o(first NOT subquery in the FROM clause);

[0210] Sub-tables and related conditions:

[0211] users u(Left Join, join condition o.user_id = u.user_id);

[0212] Order_items oi(Inner Join, join condition o.Order_id = oi.Order_id);

[0213] products p(Inner Join, join condition oi.product_id = p.product_id);

[0214] Where condition splitting:

[0215] Main table Orders: o.id In(:id); / / For example: 1,2,3,4,5,6,7,8,9,10.....

[0216] Subtable users: u.user_level='VIP';

[0217] Sub-table products: p.categOry = '3C'.

[0218] (2) Decomposition and Restructuring and Atomic Execution Plan

[0219] Main table SQL (S341):

[0220] Select o.id,o.Order_id,o.user_id,o.Order_time,o.total_amount

[0221] From Orders

[0222] Where o.id In(:id) (Returns: Assuming there are 1000 order records that meet the criteria)

[0223] Subtable SQL(S342):

[0224] In sub-table 1 users u

[0225] Select u.user_id,u.user_name,u.phOne

[0226] From users u

[0227] Where u.user_id In(:user_id_list) And u.user_level = 'VIP' (:user_id_list is the set of o.user_id returned by the main table);

[0228] In sub-table 2 Order_items oi

[0229] Select oi.Order_id,oi.product_id,oi.quantity

[0230] From Order_items oi

[0231] Where oi.Order_id In(:Order_id_list)(:Order_id_list is the set of o.Order_id returned by the main table);

[0232] In sub-table 3products p

[0233] Select p.product_id,p.product_name,p.price

[0234] From products p

[0235] Where p.product_id

[0236] In(:product_id_list)And p.categOry='3C'(:product_id_list is the set of oi.product_id returned by the sub-table oi).

[0237] (3) Memory data assembly process

[0238] Initialize the first intermediate result set: based on 1000 order records from the main table;

[0239] Merging the sub-table `users`: Data from the `user_id` sub-table is matched and merged into the main table. Data from the sub-table `users` that is not matched is filled with null values ​​in its information fields. At this point, the second intermediate result set contains 1000 rows in the main table and the newly added `users` sub-table field.

[0240] Merging the sub-table Order_items: Matching by Order_id, 800 records from the sub-table Order_items were found. These records were then merged into the main table. Records not found in the sub-table Order_items were removed using Inner Join semantics. The intermediate result set now contains 800 records, and a new sub-table Order_items field has been added.

[0241] Merging the sub-table `products`: Matching by `product_id` returned 600 records from the `products` sub-table. Records not found in the sub-table were removed using Inner Join semantics. The final intermediate result set contains 600 records, including all fields (order + user + order item + product).

[0242] (4) Circuit breaker trigger scenarios

[0243] Memory circuit breaker: If, in the above example, the number of data entries in the sub-table 'products' to be merged exceeds 10,000 (exceeding the threshold K = 10,000), then the memory circuit breaker will be triggered.

[0244] Timeout Circuit Breaker: If the query assembly time reaches 35 seconds (exceeding the threshold T = 30 seconds) due to the large amount of data during the sub-table query or merging process, TimeoutException will be triggered, the error table will be recorded, and the process will be retried. If all 3 retries time out (e.g., network fluctuations cause each time to take more than 30 seconds), an alarm will be issued and manual intervention will be required.

[0245] Actual testing has shown that this invention has significant advantages over traditional Join methods (especially for multi-Join query scenarios with tens of millions of data points):

[0246] Significantly improved query performance: query response time reduced by 30%-50%; increased system throughput: concurrent processing capacity increased by 2-3 times; enhanced stability: built-in circuit breaker mechanism effectively prevents system crashes; improved resource utilization: memory usage reduced by 50%, supporting the processing of tens of millions of data points.

[0247] Of course, those skilled in the art will recognize that the present invention is not limited to the details of the exemplary embodiments described above, but also includes the same or similar structures that can be implemented in other specific forms without departing from the spirit or essential characteristics of the invention. Therefore, the embodiments should be considered illustrative and non-limiting in all respects, and the scope of the invention is defined by the appended claims rather than the foregoing description. Thus, all variations falling within the meaning and scope of equivalents of the claims are intended to be included within the present invention. No reference numerals in the claims should be construed as limiting the scope of the claims.

[0248] Furthermore, it should be understood that although this specification describes embodiments, not every embodiment contains only one independent technical solution. This narrative style is merely for clarity. Those skilled in the art should consider the specification as a whole, and the technical solutions in each embodiment can also be appropriately combined to form other embodiments that can be understood by those skilled in the art.

[0249] The technologies, shapes, and structures not described in detail in this invention are all known technologies.

Claims

1. A performance optimization method for SQL multi-table join queries based on syntax tree reconstruction, characterized in that, include: S1: Obtain the SQL multi-table join query statement and construct the syntax tree; the core nodes of the syntax tree include the SelectItem list, FromItem object, Join object linked list, ExpressIn object, and DistInct keyword; S2: Meta-information extraction based on syntax tree; S3: Based on the syntax tree metadata, SQL multi-table join queries are split and reconstructed into single-table queries, and atomic execution plans are generated; the atomic execution plan includes the SQL query statements for the main table and the sub-table; S4: Connect to the database based on the SQL query statements of the main table and the sub-table, execute them, and assemble them in memory to obtain the query results of multiple tables.

2. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 1, characterized in that, In step S1, the SQL multi-table join query statement is obtained, and a syntax tree is constructed, including: S11: Obtain the SQL multi-table join query statement and clean it to obtain the original SQL multi-table join query statement; S12: The JSQLParser parsing tool is used to generate a token stream from the original SQL multi-table join query statement. The token stream is a series of meaningful syntactic units decomposed according to the syntax rules. S13: Construct a syntax tree using a token stream; The semantics of the core nodes in the syntax tree are as follows: The SelectItem list stores the column expressions selected by the Select property. The FromItem object records the main table information of the From clause, which includes the regular table name and the subquery expression. The Join object linked list stores Join conditions and sub-table information; The ExpressiOn object stores the Where condition.

3. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 2, characterized in that, S2: Meta-information extraction based on the normal tree, including: S21: Perform table relationship identification; S22: Based on the SelectItem list and the table's alias information, query the column objects and construct the column mapping relationship. The column objects include the table name, column name, and column alias. The mapping relationship format is: table name.column name→column alias; S23: Extract Join conditions: Search each Join clause expression based on the Join object linked list and obtain the associated fields after the On keyword; S24: Extracting Distinct keywords based on hierarchical syntax trees; S21: Perform table relationship identification, including: S211: Obtain main table information based on FromItem object, identify the type of main table, and determine whether the main table is a regular table or a subquery; S212: Analyze all Join relationships and identify sub-tables based on the Join object linked list. Sub-tables are the tables between the Join keyword and the On keyword. S213: Stores alias information for the main table and child tables.

4. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 3, characterized in that, S3: Based on syntax tree metadata, SQL multi-table join queries are split and reconstructed into single-table queries, and atomic execution plans are generated, including: S31: Perform main table identification: Identify the main table in the From clause and use it as the base table of the atomic execution plan. Select the first non-subquery in the From clause as the main table; if the main table is a subquery, directly use the subquery expression as the main table object. S32: Construct a topological sorting structure based on a linked list of Join objects, including: S321: Identify the Join type, which includes Left Join, Cross Join, and Inner Join. S322: Construct inter-table dependencies based on information from the main table and child tables, join conditions, and join types, and generate a topological sorting structure; S33: Based on the topological sorting structure and the Where condition, push down the Where condition expression to construct the mapping relationship between the Where condition expression and the table; S34: Generate atomic execution plans based on the mapping relationship between topological sorting structure, conditional expressions, and tables.

5. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 4, characterized in that, S34: Generate atomic execution plans based on the mapping relationship between the topological sorting structure and conditional expressions and tables, including: S341: Construct the main table SQL query statement based on the identified main table, column mapping relationship, and WHERE condition expression mapping relationship with the table; S342: Convert the Join condition into an In query and construct it into the Where condition of the sub-table to obtain the SQL query statement of the sub-table.

6. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 5, characterized in that, S4: Connect to the database based on the SQL query statements of the main table and child tables, execute the query, and assemble the data in memory to obtain the results of the multi-table query, including: S41: Obtain the main table parameters input by the user; perform parameter count verification; if the number of parameter counts exceeds the preset parameter count threshold N, issue an alarm and require manual intervention; S42: Bind the user-input parameters to the main table SQL query statement, connect to the database to execute the main table SQL query statement, and obtain the main table SQL query results; S43: Extract the values ​​of the related fields from all sub-table SQL query statements, including: S44: Perform parallel queries on all sub-tables and obtain the SQL query results for all sub-tables; S45: Merge the SQL query results of all tables to generate the final SQL multi-table join query result.

7. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 6, characterized in that, S43: Extract the values ​​of the related fields from all sub-table SQL query statements, including: S431: Parse and extract the IN field of all sub-table SQL query statements; S432: Traverse the SQL query results of the main table, mark the values ​​of the fields associated with the child table, and filter out null values.

8. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 7, characterized in that, S44: Perform parallel queries on all sub-tables and retrieve the SQL query results for all sub-tables, including: S441: Look up database metadata based on sub-table name, obtain statistical information for all sub-tables, estimate the size of the result set for each sub-table based on the statistical information and conditional expression for each sub-table, and pre-allocate memory space for the result set of each sub-table; the statistical information includes the total number of rows in the table, the index cardinality, and the data page size; S442: Bind the value of the associated field as the key value of the main table to the IN parameter of the SQL query of the child table; S443: Use a thread pool to concurrently execute SQL query statements for multiple sub-tables and obtain the SQL query results for all sub-tables; if the execution time exceeds the time consumption threshold, throw a timeout exception and record it; continue to retry; if multiple queries fail, issue an alarm and require manual intervention. S444: Grouping sub-table SQL query results using composite keys with multiple fields; S445: Load the SQL query results of the grouped sub-tables in batches.

9. The SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in claim 8, characterized in that, S45: Join and merge the SQL query results of all tables to generate the final SQL multi-table join query result, including: S451: Based on the topological sorting structure, the composite key of each sub-table, and the SQL query results of each sub-table, obtain the matching data of each sub-table; S452: Based on the SQL query results of the main table and the matching data of each sub-table, perform column relationship mapping and merge; S453: Based on the Join type, merges data from all tables to generate the final SQL multi-table join query result.

10. A readable medium, characterized in that, It includes a memory and a processor. The memory stores a computer program, and the processor is connected to the memory. The processor is used to execute the computer program to implement the SQL multi-table join query performance optimization method based on syntax tree reconstruction as described in any one of claims 1-9.

Images