SQL syntax tree-based fragmentation key extraction method and device, equipment and medium
By transforming SQL statements into abstract syntax trees and parsing node expressions to obtain sharding keys, the problem of inaccurate sharding key extraction under complex SQL statements in existing technologies is solved, thereby improving the query accuracy and stability of distributed databases.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-29
- Publication Date
- 2026-03-24
AI Technical Summary
Existing methods for extracting shard keys cannot effectively adapt to nested queries, nested functions, and cross-table joins in complex SQL statements, resulting in inaccurate shard key extraction and affecting query efficiency and system stability.
The target SQL statement is transformed into an abstract syntax tree. The candidate node set is obtained by traversing the abstract syntax tree, and the expression of the node is parsed to obtain the key value or value range of the sharding key. Database type recognition and syntax rule base loading are used for AST adaptation to solve the syntax differences between different databases.
It enables complete shard key extraction for complex SQL statements, improving query accuracy and system stability of distributed databases, and reducing maintenance costs and adaptation cycles.
Smart Images
Figure CN121722784A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database technology, and more specifically, to a method, apparatus, device, and medium for extracting shard keys based on an SQL syntax tree. Background Technology
[0002] With the explosive growth of data volume, the storage capacity and concurrency capabilities of a single database quickly reach their limits. The industry commonly adopts "sharding" technology, which breaks down massive amounts of data into multiple smaller pieces, and then distributes these pieces evenly across different nodes based on the value of a key field, usually called the shard key. The accuracy of the shard key directly determines the correctness of data routing, thus affecting query efficiency and system stability.
[0003] Currently, the mainstream methods for extracting shard keys still mainly rely on "surface string matching + fixed rule table": first scan the SQL text, and then use regular expressions or keyword templates to extract fields. This method can handle scenarios with fixed field positions and simple syntax, but it essentially only "looks at the character shape" and cannot penetrate complex structures such as parentheses, functions, and subqueries.
[0004] However, in real-world business scenarios, the complexity of SQL statements is increasing, such as nested queries, nested functions, and cross-table joins, which reduces the adaptability of existing shard key extraction methods to complex SQL. Summary of the Invention
[0005] This application provides a method, apparatus, device, and medium for extracting shard keys based on SQL syntax trees, in order to address the increasing complexity of SQL statements in real-world business scenarios, such as nested queries, nested functions, and cross-table joins, and to reduce the adaptability of existing shard key extraction methods to complex SQL.
[0006] Firstly, this application provides a method for extracting shard keys based on an SQL syntax tree, including:
[0007] The target SQL statement is transformed into an abstract syntax tree; wherein the abstract syntax tree contains syntactic structure and node-level semantic information.
[0008] Traverse the abstract syntax tree to obtain a set of candidate nodes containing sharding keys;
[0009] For each node in the candidate node set, the key value or value range of the sharding key corresponding to the node is obtained by parsing the expression of the node.
[0010] In one possible implementation, traversing the abstract syntax tree to obtain a set of candidate nodes containing sharding keys includes:
[0011] Read the preset sharding rule configuration, wherein the sharding rule configuration includes the sharding table name and the corresponding sharding key field;
[0012] Using the sharding rule configuration as the filtering basis, identify the table nodes to be scanned that are matched by the sharding table names from the abstract syntax tree;
[0013] Traverse the child nodes of the table node to be scanned to obtain the condition node, function node, or assignment node containing the shard key field;
[0014] Add the condition node, function node, or assignment node containing the shard key field to the candidate node set.
[0015] In one possible implementation, parsing the expression of the node to obtain the key value or range of the sharding key corresponding to the node includes:
[0016] Obtain the node type of the node, wherein the node type includes simple condition type and nested function type;
[0017] When the node type is the simple condition type, obtain the comparison expression of the node;
[0018] The sharding key field is determined based on the left operand field of the comparison expression, and the key value is determined based on the literal of the right operand field of the comparison expression.
[0019] When the node type is the nested function type, the expression of the node is parsed, and the value range of the shard key field is derived.
[0020] In one possible implementation, parsing the expression of the node and deriving the value range of the shard key field includes:
[0021] Parse the expression of the node to obtain the function name and all corresponding parameters, and determine the position of the parameter containing the sharding key field;
[0022] In the preset function inverse relationship, the inverse rule corresponding to the function name is retrieved, wherein the inverse rule is used to indicate the range mapping relationship between the function output range and the input parameter range;
[0023] Substitute the function output range into the reverse calculation rule to calculate the range of the parameter at the specified position, and use the range of the parameter as the implicit range of the sharding key field.
[0024] In one possible implementation, after traversing the abstract syntax tree to obtain a set of candidate nodes containing sharding keys, the method further includes:
[0025] In the set of candidate nodes, the target node is determined based on the node located in the join condition subtree and referencing two different tables simultaneously;
[0026] Extract the join condition expression from the target node to obtain the left and right operation fields located on both sides of the operator within the join condition expression, and read the preset mapping relationship between aliases and table names to determine the actual table to which the left and right operation fields belong.
[0027] If one of the left operation fields and the right operation fields is a configured sharding key of the main table, and the other field is located in the associated table and has the same field name, then the corresponding field in the associated table will be marked as an implicit sharding key.
[0028] Extract the key value or value range of the hidden fragmentation key.
[0029] In one possible implementation, the step of converting the target SQL statement into an abstract syntax tree includes:
[0030] Determine the database type corresponding to the target SQL statement;
[0031] Load the corresponding syntax rule library according to the database type;
[0032] The appropriate parser is loaded based on the syntax rule base, and the target SQL statement is transformed into an abstract syntax tree by the parser.
[0033] In one possible implementation, loading the corresponding syntax rule base according to the database type includes:
[0034] Retrieve the preset mapping table between database types and syntax rule base;
[0035] Based on the mapping table between the database type and the syntax rule base, query the syntax rule base corresponding to the database type;
[0036] Load the syntax rule library.
[0037] Secondly, this application provides a shard key extraction device based on an SQL syntax tree, the device comprising: a conversion module, an acquisition module, and an extraction module, wherein:
[0038] The conversion module is used to convert the target SQL statement into an abstract syntax tree, wherein the abstract syntax tree contains syntactic structure and node-level semantic information;
[0039] The acquisition module is used to traverse the abstract syntax tree and acquire a set of candidate nodes containing sharding keys;
[0040] The extraction module is used to obtain the key value or value range of the sharding key corresponding to each node in the candidate node set by parsing the expression of the node.
[0041] In one possible implementation, the acquisition module is further configured to:
[0042] Read the preset sharding rule configuration, wherein the sharding rule configuration includes the sharding table name and the corresponding sharding key field;
[0043] Using the sharding rule configuration as the filtering basis, identify the table nodes to be scanned that are matched by the sharding table names from the abstract syntax tree;
[0044] Traverse the child nodes of the table node to be scanned to obtain the condition node, function node, or assignment node containing the shard key field;
[0045] Add the condition node, function node, or assignment node containing the shard key field to the candidate node set.
[0046] In one possible implementation, the extraction module is further configured to:
[0047] Obtain the node type of the node, wherein the node type includes simple condition type and nested function type;
[0048] If the node type is the simple condition type, obtain the comparison expression of the node;
[0049] The sharding key field is determined based on the left operand field of the comparison expression, and the key value is determined based on the literal of the right operand field of the comparison expression.
[0050] When the node type is the nested function type, the expression of the node is parsed, and the value range of the shard key field is derived.
[0051] In one possible implementation, the extraction module is further configured to:
[0052] Parse the expression of the node to obtain the function name and all corresponding parameters, and determine the position of the parameter containing the sharding key field;
[0053] In the preset function inverse relationship, the inverse rule corresponding to the function name is retrieved, wherein the inverse rule is used to indicate the range mapping relationship between the function output range and the input parameter range;
[0054] Substitute the function output range into the reverse calculation rule to calculate the range of the parameter at the specified position, and use the range of the parameter as the implicit range of the sharding key field.
[0055] In one possible implementation, the extraction module is further configured to:
[0056] In the set of candidate nodes, the target node is determined based on the node located in the join condition subtree and referencing two different tables simultaneously;
[0057] Extract the join condition expression from the target node to obtain the left and right operation fields located on both sides of the operator within the join condition expression, and read the preset mapping relationship between aliases and table names to determine the actual table to which the left and right operation fields belong.
[0058] If one of the left operation fields and the right operation fields is a configured sharding key of the main table, and the other field is located in the associated table and has the same field name, then the corresponding field in the associated table will be marked as an implicit sharding key.
[0059] Extract the key value or value range of the hidden fragmentation key.
[0060] In one possible implementation, the extraction module is further configured to:
[0061] Determine the database type corresponding to the target SQL statement;
[0062] Load the corresponding syntax rule library according to the database type;
[0063] The appropriate parser is loaded based on the syntax rule base, and the target SQL statement is transformed into an abstract syntax tree by the parser.
[0064] In one possible implementation, the extraction module is further configured to:
[0065] Retrieve the preset mapping table between database types and syntax rule base;
[0066] Based on the mapping table between the database type and the syntax rule base, query the syntax rule base corresponding to the database type;
[0067] Load the syntax rule library.
[0068] Thirdly, this application provides an electronic device, including: a processor, and a memory communicatively connected to the processor;
[0069] The memory stores computer-executed instructions;
[0070] The processor executes computer execution instructions stored in the memory to implement the SQL syntax tree-based shard key extraction method as described in the first aspect.
[0071] Fourthly, this application provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a computer, are used to implement the shard key extraction method based on the SQL syntax tree as described in the first aspect.
[0072] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, can implement the steps of the scheme recommendation method as described in any of the first aspects.
[0073] This invention provides a method, apparatus, device, and medium for extracting shard keys based on an SQL syntax tree. The method transforms a target SQL statement into an abstract syntax tree containing syntactic structure and node-level semantic information. It then determines a set of candidate nodes containing the shard key from the abstract syntax tree, parses the expression of each node in the candidate node set, and obtains the key value or value range of the shard key. Through this method, the hierarchical structure of the abstract syntax tree allows for complete parsing of the SQL syntax logic, ensuring that even if the SQL statement contains nested queries, nested functions, or cross-table joins, the shard key can still be completely extracted. This improves the adaptability of the solution to complex SQL statements and enhances the query accuracy and system stability of distributed databases. Attached Figure Description
[0074] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.
[0075] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0076] Figure 1 A flowchart illustrating a method for extracting shard keys based on an SQL syntax tree, provided in this application embodiment. Figure 1 ;
[0077] Figure 2 A flowchart illustrating a method for extracting shard keys based on an SQL syntax tree, provided in this application embodiment. Figure 2 ;
[0078] Figure 3 A schematic diagram of a fragment key extraction device based on an SQL syntax tree provided in an embodiment of this application;
[0079] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation
[0080] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0081] Currently, mainstream methods for extracting shard keys still primarily rely on "surface string matching + fixed rule tables": first scanning the SQL text, then using regular expressions or keyword templates to extract fields. This method is adequate for scenarios with fixed field positions and simple syntax, but it essentially only "looks at the character shape" and cannot penetrate complex structures such as parentheses, functions, and subqueries. However, in real-world business scenarios, the complexity of SQL statements is increasing, including nested queries, nested functions, and cross-table joins, reducing the adaptability of existing shard key extraction methods to complex SQL.
[0082] This invention provides a shard key extraction method based on an SQL syntax tree. The method transforms the target SQL statement into an abstract syntax tree containing syntactic structure and node-level semantic information. A set of candidate nodes containing the shard key is determined from the abstract syntax tree, and the expression of each node in the candidate node set is parsed to obtain the key value or value range of the shard key. Through this method, the hierarchical structure of the abstract syntax tree allows for complete parsing of the SQL syntax logic, ensuring that even if the SQL statement contains nested queries, nested functions, or cross-table joins, the shard key can still be completely extracted. This improves the adaptability of the solution to complex SQL statements and enhances the query accuracy and system stability of distributed databases.
[0083] Figure 1 A flowchart illustrating a method for extracting shard keys based on an SQL syntax tree, provided in this application embodiment. Figure 1 .like Figure 1 As shown, the method includes:
[0084] S101. Transform the target SQL statement into an abstract syntax tree.
[0085] The abstract syntax tree contains grammatical structure and node-level semantic information.
[0086] In this step, with the explosive growth of data volume, the storage capacity and concurrency capabilities of a single database quickly reach their limits. The industry commonly uses sharding technology to break down massive amounts of data into multiple smaller pieces, then distributes these pieces evenly across different nodes based on the value of a key field, often called the shard key. The accuracy of the shard key directly determines the correctness of data routing, thus affecting query efficiency and system stability. Currently, the mainstream shard key extraction method still relies on "surface string matching + fixed rule tables": first scanning the SQL text, then using regular expressions or keyword templates to extract fields. This method is adequate for scenarios with fixed field positions and simple syntax, but it essentially only "looks at the character shape" and cannot penetrate complex structures such as brackets, functions, and subqueries. However, in real-world business scenarios, the complexity of SQL statements is increasing, such as nested queries, nested functions, and cross-table joins, reducing the adaptability of existing shard key extraction methods to complex SQL.
[0087] Abstract Syntax Trees (ASTs) can break down an entire SQL statement into hierarchically connected nodes: each level of nodes carries keywords, functions, subqueries, or join conditions, with fixed and unique parent-child and sibling relationships. With this tree structure, parsing logic is no longer affected by text order, bracket depth, or alias variations; it only needs to access nodes in depth or breadth order to accurately find sharding keys that are wrapped in functions, renamed, or scattered across multiple tables at any nesting level. Simultaneously, the semantic tags attached to nodes allow the system to directly read function names, the number of parameters, and comparison operators, and then deduce the value range of the fields. Therefore, regardless of how nested the SQL statement is, how stacked the functions are, or how intersecting the joins are, the AST provides a stable, complete, and traversable view, ensuring that sharding key extraction under complex syntax is as accurate as under simple statements. Therefore, this embodiment introduces an AST for sharding key extraction.
[0088] Specifically, the target SQL statement is first fed into the lexical analyzer, which segments it into elements such as keywords, identifiers, operators, and basic constant symbols according to the character stream. Then, the parser recursively identifies each syntactic unit, such as select lists, from clauses, where conditions, joins, function calls, and nested subqueries, according to preset database dialect rules, and generates a corresponding tree node for each unit. The node attributes record the node type (such as function, comparison, logical operation), field name, alias, literal value, operator type, and child node pointers, thus preserving both the complete syntactic hierarchy and semantic tags for subsequent traversal within an abstract syntax tree.
[0089] Target SQL statement: The SQL query statement to be parsed, either input by the user or received by the database middleware, such as "SELECT * FROM orders o JOIN users u ON o.user_id = u.user_id WHERE SUBSTR(o.order_id,1,2)='01'".
[0090] Abstract Syntax Tree (AST): A structured representation of SQL statements, where node types correspond one-to-one with SQL syntax elements (such as table names, field names, and conditional expressions). For example, the AST corresponding to the SQL statement "SELECT * FROM t_order WHERE order_id = 100" contains the table node "t_order", the field node "order_id", and the value node "100". AST will be used instead of Abstract Syntax Tree in the following explanations.
[0091] In one possible implementation, in a cross-database deployment scenario, the same shard key extraction logic must address multiple database dialects. Different products differ in function names, keywords, parameter order, and even syntax. If the existing approach of "one hard-coded parser for each database" is adopted, independent parsing branches need to be repeatedly developed, tested, and maintained for each new database, resulting in long adaptation cycles, high code redundancy, and a linearly increasing cost for subsequent upgrades and bug fixes. Therefore, this embodiment introduces a dynamic adaptation mechanism for the AST parser based on database dialects. It loads the corresponding syntax rule library according to the database type of the target SQL statement, generating an AST that conforms to the syntax specifications of the target database. The specific implementation steps are as follows:
[0092] Determine the database type corresponding to the target SQL statement;
[0093] Load the corresponding syntax rule library based on the database type;
[0094] The parser is loaded based on the syntax rule base and then used to transform the target SQL statement into an abstract syntax tree.
[0095] Database type: The database system to which the target SQL statement belongs, such as MySQL, PostgreSQL, or SQL Server.
[0096] Syntax rule base: A rule base that stores the syntax specifications of different databases, such as the syntax rule files of ANTLR.
[0097] Adapted AST: An AST structure that conforms to the syntax specifications of the target database.
[0098] By adopting a dynamic adaptation mechanism for the AST parser based on database dialects, new databases only need to provide the corresponding syntax rule library file. The parser can then instantly identify their unique functions (such as string truncation and date conversion) and syntactic differences, achieving "develop once, use across multiple databases." This significantly reduces adaptation and maintenance costs, shortens product delivery cycles, and ensures that the shard key extraction logic can obtain a complete and accurate syntax tree in various database environments, improving the scalability and stability of the distributed system.
[0099] In another possible implementation, a mapping table between database types and syntax rule bases can be pre-configured. When it is necessary to determine the syntax rule base corresponding to a database type, the mapping table is queried to obtain it. Dynamic adaptation of the syntax rule base is achieved through querying and loading the mapping table between database types and syntax rule bases.
[0100] The specific implementation steps are as follows:
[0101] Retrieve the preset mapping table between database types and syntax rule base;
[0102] Based on the mapping table between database types and syntax rule bases, query the syntax rule base corresponding to the database type;
[0103] Load the syntax rule library.
[0104] Mapping table between database types and syntax rule bases: A table that stores the mapping relationship between database types and corresponding syntax rule bases, such as "MySQL-ANTLR_SQL_grammar_MySQL.g4".
[0105] Load the syntax rule base: Load the retrieved syntax rule base into the SQL parser for AST generation.
[0106] S102. Traverse the abstract syntax tree to obtain a set of candidate nodes containing sharding keys.
[0107] In this step, the system performs a depth-first traversal of the AST starting from the root node. Whenever a node containing a conditional expression, function call, or table join is encountered, its field names, aliases, and parameter lists are checked to see if they match the preset sharding rule configuration. If they do, the node and its parent path are added to the candidate node set until the entire AST has been scanned, thus collecting all nodes that may carry sharding keys at once for subsequent semantic analysis and range deduction.
[0108] Nodes related to the shard key: Nodes in the AST that are directly or indirectly related to the shard key field, including condition nodes (such as conditional expressions in the WHERE clause), function nodes (such as function operations in which the shard key is used as a parameter), and assignment nodes (such as assignment expressions in INSERT or UPDATE statements).
[0109] In one possible implementation, to achieve accurate location of nodes containing sharding keys and make the resulting candidate node set more reliable and accurate, precise node location is achieved through preset sharding rule configuration and AST child node traversal. The implementation steps are as follows:
[0110] Read the preset sharding rule configuration, which includes the sharding table name and the corresponding sharding key field;
[0111] Based on the sharding rule configuration, the node of the table to be scanned that is matched by the sharded table name is identified from the abstract syntax tree;
[0112] Iterate through the child nodes of the table node to be scanned and obtain the condition node, function node, or assignment node that contains the shard key field;
[0113] Add the condition node, function node, or assignment node containing the shard key field to the candidate node set.
[0114] The sharding rule configuration stores the preset rules for the "table name-sharding key" mapping. For example, the configuration file defines the sharding key of the "t_order" table as "order_id".
[0115] Condition nodes: Nodes in the AST that represent conditional expressions, such as "order_id=100" or "SUBSTR(order_id,1,2)='01'" in the WHERE clause.
[0116] Function nodes: Nodes in the AST that represent function calls, such as "SUBSTR(order_id,1,2)" or "hash(user_id)".
[0117] Assignment node: A node in the AST that represents an assignment operation, such as “VALUES(100)” in the INSERT statement or “SET order_id=200” in the UPDATE statement.
[0118] Specifically, the system first reads the pre-configured sharding rules to obtain the "table-field" mapping relationship. Then, it performs a depth-first scan of the abstract syntax tree starting from the root node. Once a node is a table reference and its name matches the configuration, that node is set as the "scan entry point," i.e., the node to be scanned. For all child nodes under the node to be scanned, the system checks them level by level in the order of "condition → function → assignment": when encountering a condition node, it compares the field names of the left and right operands; when encountering a function node, it recursively checks its parameter list; when encountering an assignment node, it compares the target being assigned. If any field name matches the sharding key field in the configuration, the current node is immediately added to the candidate set, and its node type, depth, and parent pointer are recorded for subsequent semantic deduction. Through the two steps of "locating the node to be scanned + subtree traversal," the sharding keys in each clause such as WHERE, SELECT, ON, and HAVING are captured all at once, avoiding the extraction omissions caused by traditional methods that only scan the WHERE clause and miss function parameters or nested assignments.
[0119] S103. For each node in the candidate node set, obtain the key value or value range of the sharding key corresponding to the node by parsing the node's expression.
[0120] In this step, for each node in the candidate node set, processing is first divided according to its type: If the node is a simple comparison, i.e., a node of simple condition type, the left-hand value field name is directly extracted and matched with the sharding rule. If the match is successful, the right-hand value literal is used as the key. If the node is a function call, i.e., a node of nested function type, the function parameters are parsed to deduce the range of the sharding key field. Finally, the explicit key-value or implicit value range is written into the sharding routing vector as "field-value" pairs, completing one full node parsing.
[0121] This invention provides a shard key extraction method based on an SQL syntax tree. It transforms the target SQL statement into an abstract syntax tree containing syntactic structure and node-level semantic information. A set of candidate nodes containing the shard key is determined from the abstract syntax tree, and the expression of each node in the candidate node set is parsed to obtain the key value or value range of the shard key. Through this method, the hierarchical structure of the abstract syntax tree allows for complete parsing of the SQL syntax logic, ensuring that even if the SQL statement contains nested queries, nested functions, or cross-table joins, the shard key can still be completely extracted. This improves the adaptability of the solution to complex SQL statements and enhances the query accuracy and system stability of distributed databases. By identifying the database type and loading the syntax rule base, it achieves AST adaptation generation in cross-database scenarios, solving the AST generation error problem caused by differences in the syntax of different databases in traditional methods. Simultaneously, by querying and loading the mapping table between database types and the syntax rule base, it achieves dynamic adaptation of the syntax rule base, solving the problem of high maintenance costs caused by manually adapting different databases in traditional methods.
[0122] Figure 2 A flowchart illustrating a method for extracting shard keys based on an SQL syntax tree, provided in this application embodiment. Figure 2 This embodiment provides a detailed explanation of the steps for resolving nodes to obtain the key value or range of the sharding key. For example... Figure 2 As shown, the method includes:
[0123] S201. Get the node type of the node.
[0124] Among them, node types include simple condition types and nested function types.
[0125] In this step, data sharding is the core mechanism for achieving horizontal scaling in a distributed database system. Since SQL query conditions may be presented in different forms, such as direct comparisons or nested functions, traditional single extraction rules can lead to shard key identification failures. This system dynamically identifies node types and employs a differentiated extraction strategy to ensure accurate location of the target shard even in complex query scenarios, avoiding the performance overhead of full table scans.
[0126] Specifically, the system scans each node in the candidate node set. If the node structure satisfies the field name, operator, and literal value, such as order_id=100, it is marked as a simple condition type. If a function call node is detected, such as SUBSTR(order_id,1,2), it is classified as a nested function type.
[0127] S202. When the node type is a simple condition type, obtain the comparison expression of the node. Determine the sharding key field based on the left operand field of the comparison expression, and determine the key value based on the literal of the right operand field of the comparison expression.
[0128] In this step, when the node type is determined to be a simple condition type, the system first identifies the comparison expression in the node, and then reads its left-hand value field and right-hand value literal. The sharding key field is determined based on the left-hand value field; the right-hand value literal is used as the key value of the sharding key field, and the "field-value" pair is written into the sharding routing vector for subsequent routing calculations.
[0129] For example, in the comparison expression “order_id=100”, the parser recognizes the left-value field “order_id” as the shard key field and the right-value literal “100” as the key value, and finally generates the routing condition order_id=100, thereby accurately locating the target shard.
[0130] S203. When the node type is a nested function type, parse the node expression to obtain the function name and all corresponding parameters, and determine the position of the shard key field in the parameter.
[0131] In this step, when a node type is determined to be a nested function, the system first reads the node's name field, i.e., the function name, and then iterates through its parameter list. Each parameter node is compared with the preset sharding rule configuration. If the parameter node is a field reference and the field name matches the sharding key field, the system records the parameter's index in the list and marks it as the "key position," while caching the parameter's data type to provide a type basis for subsequent range conversions.
[0132] S204. In the preset function inverse relationship, retrieve the inverse rule corresponding to the function name, substitute the function output value range into the inverse rule, calculate the value range corresponding to the parameter at the position in reverse, and use the value range as the implicit range of the sharding key field.
[0133] Among them, the reverse calculation rule is used to indicate the range mapping relationship between the function's output range and the input parameter range.
[0134] In this step, the system uses the function name as the key to retrieve the corresponding reverse deduction rule from the preset "function reverse deduction relationship". This reverse deduction rule contains the value range mapping relationship between the function's output value range and the input parameter value range, including attributes such as the reverse conversion formula, interval merging strategy, and prefix length. If a match is found, the constant result value (such as '01') on the right side of the function in the target SQL statement is substituted into the reverse deduction rule to deduce the value range of the shard key field. For truncation functions, this yields "the set of strings starting with a constant value"; for hash functions, it yields "all original numerical ranges where the hash value equals a constant". After deduction, the system writes the obtained value range into the shard routing vector in the form of a closed interval or a prefix set, allowing the distributed routing layer to directly locate the target shard, thus completing the extraction of the complete key range under nested function conditions.
[0135] In another possible implementation, the existing shard key extraction logic only focuses on the main table fields. When encountering join queries like "order table o JOIN user table u ON o.user_id=u.user_id", it fails to recognize that u.user_id is synonymous with the main table shard key o.user_id. As a result, it misses shard information from the join table side, leading to incomplete routing, data retrieval across nodes, and a decrease in query performance. Therefore, this embodiment solves the problem of insufficient identification of implicit cross-table shard key relationships by analyzing the mapping relationship between aliases and table names and JOIN conditions.
[0136] The implementation steps are as follows:
[0137] In the candidate node set, the target node is determined based on the node located in the join condition subtree and referencing two different tables simultaneously;
[0138] Extract the join condition expression from the target node to obtain the left and right operation fields located on both sides of the operator within the join condition expression, and read the pre-defined mapping relationship between aliases and table names to determine the actual table to which the left and right operation fields belong.
[0139] If one of the left and right operation fields is a configured sharding key in the main table, and the other field is in the related table with the same field name, then the corresponding field in the related table will be marked as an implicit sharding key.
[0140] Extract the key value or value range of the hidden fragmentation key.
[0141] By using the three steps of "connection condition subtree filtering - alias mapping - field name comparison", the system automatically binds the shard key of the related table to the routing logic of the main table for the first time. This allows a single related query to hit only the single shard common to both the main table and the related table, avoiding cross-shard broadcasting and significantly improving query completeness and cluster resource utilization.
[0142] For example, in the SQL "SELECT * FROM orders o JOIN users u ON o.user_id=u.user_id", if the sharding key of the orders table is user_id, and the sharding key of the users table is also user_id, the parser will identify the relationship between u.user_id and o.user_id through the JOIN condition, and bind u.user_id as the implicit sharding key to the sharding key extraction process of the orders table.
[0143] This invention provides a method for extracting shard keys based on SQL syntax trees. It employs a dual-path refined extraction approach: simple conditions and nested functions. For simple conditions, the key value is directly read, eliminating redundant calculations. For nested functions, the method locates the parameter position of the shard key fragment and invokes pre-defined reverse rules to convert the function output into the implicit range of the shard key field, solving the problem of missing shard keys caused by the inability to parse nested functions in traditional methods. Simultaneously, a mapping relationship between aliases and table names is established during the parsing phase, and JOIN conditions are deeply scanned to automatically bind the shard key of the main table to the corresponding field in the related table as an implicit shard key. This expands shard key extraction in join queries from a single table to a combination of the main table and related tables. Through this approach, complete capture of shard keys in complex SQL is achieved, significantly reducing the probability of cross-node broadcasting and incorrect routing, and improving the query accuracy and system stability of distributed databases.
[0144] Figure 3 This is a schematic diagram of a shard key extraction device based on an SQL syntax tree, provided as an embodiment of this application. Figure 3As shown, the SQL syntax tree-based shard key extraction device 30 includes: a conversion module 301, an acquisition module 302, and an extraction module 303, wherein:
[0145] The transformation module 301 is used to transform the target SQL statement into an abstract syntax tree, wherein the abstract syntax tree contains syntactic structure and node-level semantic information;
[0146] Module 302 is used to traverse the abstract syntax tree and obtain a set of candidate nodes containing sharding keys;
[0147] Extraction module 303 is used to obtain the key value or value range of the sharding key corresponding to each node in the candidate node set by parsing the expression of the node.
[0148] In one possible implementation, the acquisition module 302 is further configured to:
[0149] Read the preset sharding rule configuration, which includes the sharding table name and the corresponding sharding key field;
[0150] Based on the sharding rule configuration, the node of the table to be scanned that is matched by the sharded table name is identified from the abstract syntax tree;
[0151] Iterate through the child nodes of the table node to be scanned and obtain the condition node, function node, or assignment node that contains the shard key field;
[0152] Add the condition node, function node, or assignment node containing the shard key field to the candidate node set.
[0153] In one possible implementation, the extraction module 303 is further configured to:
[0154] Get the node type of the node, where the node type includes simple condition type and nested function type;
[0155] When the node type is a simple condition type, retrieve the comparison expression of the node;
[0156] The sharding key field is determined based on the left operand of the comparison expression, and the key value is determined based on the literal of the right operand of the comparison expression.
[0157] When the node type is a nested function type, parse the function parameters and deduce the value range of the shard key field.
[0158] In one possible implementation, the extraction module 303 is further configured to:
[0159] Parse the node's expression to obtain the recognition function name and all its corresponding parameters, and determine the position of the parameter containing the sharding key field;
[0160] In the preset function inverse relationship, the inverse rule corresponding to the function name is retrieved. The inverse rule is used to indicate the range mapping relationship between the function output range and the input parameter range.
[0161] Substitute the function's output range into the reverse calculation rule to calculate the range corresponding to the parameter at the specified position, and use this range as the implicit range of the sharding key field.
[0162] In one possible implementation, the extraction module 303 is further configured to:
[0163] In the candidate node set, the target node is determined based on the node located in the join condition subtree and referencing two different tables simultaneously;
[0164] Parse the join condition expression of the target node to obtain the left and right operation fields located on both sides of the operator within the join condition expression, and read the pre-defined mapping relationship between aliases and table names to determine the actual table to which the left and right operation fields belong.
[0165] If one of the left and right operation fields is a configured sharding key in the main table, and the other field is in the related table with the same field name, then the corresponding field in the related table will be marked as an implicit sharding key.
[0166] Extract the key value or value range of the hidden fragmentation key.
[0167] In one possible implementation, the extraction module 303 is further configured to:
[0168] Determine the database type corresponding to the target SQL statement;
[0169] Load the corresponding syntax rule library based on the database type;
[0170] The parser is loaded based on the syntax rule base and then used to transform the target SQL statement into an abstract syntax tree.
[0171] In one possible implementation, the extraction module 303 is further configured to:
[0172] Retrieve the preset mapping table between database types and syntax rule base;
[0173] Based on the mapping table between database types and syntax rule bases, query the syntax rule base corresponding to the database type;
[0174] Load the syntax rule library.
[0175] Thirdly, this application provides an electronic device, including: a processor, and a memory communicatively connected to the processor;
[0176] The memory stores the instructions that the computer executes;
[0177] The processor executes computer execution instructions stored in memory to implement the SQL syntax tree-based shard key extraction method as described in the first aspect.
[0178] This application provides a shard key extraction device based on an SQL syntax tree, which can execute the shard key extraction method based on an SQL syntax tree provided in the above method embodiment. Its implementation principle and technical effect are similar, and will not be described in detail here.
[0179] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Figure 4 As shown, the electronic device 40 includes a processor 401 and a memory 402 communicatively connected to the processor 401. Optionally, the electronic device 40 also includes a communication component 403. The processor 401, memory 402, and communication component 403 are connected via a bus 404.
[0180] Memory 402 stores instructions executed by the computer;
[0181] The processor 401 executes computer execution instructions stored in the memory 402 to implement the shard key extraction method based on the SQL syntax tree as described above.
[0182] At least one processor 401 may be a central processing unit (CPU), an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of this application.
[0183] Optionally, in specific implementations, the processor 401 and memory 402 are implemented independently. In this case, the processor 401 and memory 402 can be interconnected via a bus to complete communication between them. The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. Buses can be categorized as address buses, data buses, control buses, etc., but this does not imply that there is only one bus or one type of bus.
[0184] Optionally, in a specific implementation, if the processor 401 and the memory 402 are integrated on a single chip, the processor 401 and the memory 402 can communicate through an internal interface.
[0185] This application also provides a computer storage medium storing computer execution instructions, which, when executed by a processor, implement the aforementioned technical solution of the shard key extraction method based on an SQL syntax tree.
[0186] The aforementioned computer-readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. The computer-readable storage medium can be any available medium accessible to a general-purpose or special-purpose computer.
[0187] An exemplary readable storage medium is coupled to a processor, enabling the processor to read information from and write information to the readable storage medium. Alternatively, the readable storage medium can be an integral part of the processor. Both the processor and the readable storage medium can reside in an Application Specific Integrated Circuit (ASIC). Alternatively, the processor and the readable storage medium can exist as discrete components within the control device of a garment handling apparatus.
[0188] This unit division is merely a logical functional division; in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be indirect coupling or communication connection through some interfaces, devices, or units, and may be electrical, mechanical, or other forms.
[0189] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.
[0190] In addition, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.
[0191] If this function is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods indicated in the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.
[0192] Those skilled in the art will understand that all or part of the steps of the above-described method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When executed, the program performs the steps of the above-described method embodiments; and the aforementioned storage medium includes various media capable of storing program code, such as ROM, RAM, magnetic disks, or optical disks.
[0193] The technical solutions of this application have been described above with reference to the preferred embodiments shown in the accompanying drawings. However, it is readily understood by those skilled in the art that the scope of protection of this application is obviously not limited to these specific embodiments. The above embodiments are only used to illustrate the technical solutions of this application and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. These modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of this application.
Claims
1. A method for extracting shard keys based on SQL syntax trees, characterized in that, include: The target SQL statement is transformed into an abstract syntax tree; wherein the abstract syntax tree contains syntactic structure and node-level semantic information; Traverse the abstract syntax tree to obtain a set of candidate nodes containing sharding keys; For each node in the candidate node set, the key value or value range of the sharding key corresponding to the node is obtained by parsing the expression of the node.
2. The method according to claim 1, characterized in that, The step of traversing the abstract syntax tree to obtain a set of candidate nodes containing sharding keys includes: Read the preset sharding rule configuration, wherein the sharding rule configuration includes the sharding table name and the corresponding sharding key field; Using the sharding rule configuration as the filtering basis, identify the table nodes to be scanned that are matched by the sharding table names from the abstract syntax tree; Traverse the child nodes of the table node to be scanned to obtain the condition node, function node, or assignment node containing the shard key field; Add the condition node, function node, or assignment node containing the shard key field to the candidate node set.
3. The method according to claim 2, characterized in that, The process of parsing the expression of the node to obtain the key value or range of the shard key corresponding to the node includes: Obtain the node type of the node, wherein the node type includes simple condition type and nested function type; When the node type is the simple condition type, obtain the comparison expression of the node; determine the shard key field according to the left operand field of the comparison expression, and determine the key value according to the literal of the right operand field of the comparison expression; When the node type is the nested function type, the expression of the node is parsed, and the value range of the shard key field is derived.
4. The method according to claim 3, characterized in that, The process of parsing the expression of the node and deriving the value range of the shard key field includes: Parse the expression of the node to obtain the function name and all corresponding parameters, and determine the position of the parameter containing the sharding key field; In the preset function inverse relationship, the inverse rule corresponding to the function name is retrieved, wherein the inverse rule is used to indicate the range mapping relationship between the function output range and the input parameter range; Substitute the function output range into the reverse calculation rule to calculate the range of the parameter at the specified position, and use the range of the parameter as the implicit range of the sharding key field.
5. The method according to claim 1, characterized in that, After traversing the abstract syntax tree to obtain a set of candidate nodes containing sharding keys, the method further includes: In the set of candidate nodes, the target node is determined based on the node located in the join condition subtree and referencing two different tables simultaneously; Extract the join condition expression from the target node to obtain the left and right operation fields located on both sides of the operator in the join condition expression, and read the preset mapping relationship between aliases and table names to determine the real table to which the left and right operation fields belong. If one of the left operation fields and the right operation fields is a configured sharding key of the main table, and the other field is located in the associated table and has the same field name, then the corresponding field in the associated table will be marked as an implicit sharding key. Extract the key value or value range of the hidden fragmentation key.
6. The method according to claim 1, characterized in that, The process of converting the target SQL statement into an abstract syntax tree includes: Determine the database type corresponding to the target SQL statement; Load the corresponding syntax rule library according to the database type; The appropriate parser is loaded based on the syntax rule base, and the target SQL statement is transformed into an abstract syntax tree by the parser.
7. The method according to claim 6, characterized in that, The step of loading the corresponding syntax rule library according to the database type includes: Retrieve the preset mapping table between database types and syntax rule base; Based on the mapping table between the database type and the syntax rule base, query the syntax rule base corresponding to the database type; Load the syntax rule library.
8. A shard key extraction device based on an SQL syntax tree, characterized in that, The device includes: a conversion module, an acquisition module, and an extraction module, wherein: The conversion module is used to convert the target SQL statement into an abstract syntax tree, wherein the abstract syntax tree contains syntactic structure and node-level semantic information; The acquisition module is used to traverse the abstract syntax tree and acquire a set of candidate nodes containing sharding keys; The extraction module is used to obtain the key value or value range of the sharding key corresponding to each node in the candidate node set by parsing the expression of the node.
9. An electronic device, characterized in that, include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executed instructions; The processor executes computer execution instructions stored in the memory to implement the method as described in any one of claims 1 to 7.
10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the SQL syntax tree-based shard key extraction method as described in any one of claims 1 to 7.