Method for fast query of tree-shaped association relationship in mass code data
By using partition key design and event-driven architecture, the cross-partition scanning and state coupling problems of tree-structured relational queries in a sharded database environment are solved. This enables efficient single-shard queries and asynchronous updates, improves query performance and system scalability, supports query requirements in multiple scenarios, and ensures data consistency.
Patent Information
- Application Number
- CN202610655457.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-05-13
- Publication Date
- 2026-07-03
AI Technical Summary
In a sharded database environment, tree-structured relational queries face challenges such as cross-partition scanning, coupling of state and relational relationships, multi-scenario query requirements, low efficiency of batch queries, and contradictions between data updates and query performance. Traditional solutions cannot efficiently cover all scenarios.
The code information table and code association table are designed with partition keys, combined with reverse linked lists and attribute mapping tables to achieve fast query within a single shard; asynchronous updates are achieved through an event-driven architecture to decouple data updates and queries, and a streaming computing engine is used for real-time processing.
It achieves performance improvements in single-code query, tree query, and batch query, supports tree query across all scenarios, has strong system scalability, high efficiency in state-integrated query, decouples data updates from queries, has strong real-time business processing capabilities, and guarantees eventual data consistency.
Smart Images

Figure CN122332430A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data processing, specifically a method for quickly querying tree-like relationships in massive amounts of code data. Background Technology
[0002] As enterprises deepen their digital transformation, digital management systems need to process massive amounts of code data and support complex multi-level relational queries (such as hierarchical relationships between pallet codes, box codes, carton codes, bottle codes, etc.). Traditional relational databases experience a sharp decline in query performance when the amount of data in a single table is huge. A common approach is to use database sharding and table partitioning techniques for horizontal data scaling.
[0003] However, after database sharding and table partitioning, tree-structured join queries face the following technical challenges:
[0004] (1) JOIN queries cause cross-partition scans: Multi-level join queries usually require join operations. In a sharded database environment, these JOINs are very likely to cause cross-partition data scans, and the response time is uncontrollable, degrading from milliseconds to seconds or even minutes.
[0005] (2) Coupling of code status and association: Codes have multiple statuses such as normal, abnormal, frozen, and abolished. Status changes require synchronous updates of association. In existing solutions, status and association are tightly coupled. Status queries require additional association queries, and updating association when status changes is complex and error-prone.
[0006] (3) Multi-scenario relational query requirements: The business needs to support multiple relational query modes: query all descendants based on the root (such as all small box codes under the toggle code), query the complete path based on the leaf (such as the complete path from the small box code to the toggle code), query the complete tree based on the node (such as the upper and lower levels of the box code), query other nodes with the same root, and query all child nodes under the node. A single data structure (such as only storing parent-child relationships) cannot efficiently cover all scenarios.
[0007] (4) Low efficiency of batch queries: In a sharded database environment, if batch queries are scattered across multiple shards, the default cross-partition execution strategy results in a large number of scans and network transmissions, leading to low query efficiency.
[0008] (5) Conflict between data update and query performance: Operations such as status change and association removal require simultaneous updates to multiple redundant tables. If executed synchronously, query performance will be affected; if executed asynchronously, data consistency issues will arise. Summary of the Invention
[0009] To address the problem of low efficiency in relational queries after database sharding and table partitioning, this invention provides a fast query method for tree-like relationships in massive code data.
[0010] The technical solution adopted by the present invention to solve the above problems is:
[0011] Methods for quickly querying tree-like relationships in massive code data include:
[0012] Step 1: Set up the code information table and code association table. All tables are partitioned based on the partition key.
[0013] The code information table includes:
[0014] Composite primary key: Consists of a key value and a reused version number;
[0015] Reuse version number: Used to identify reuse information;
[0016] Reverse linked list: Stores the complete path identifier from the current code to the root code in JSON array format;
[0017] Sibling information: includes the master node identifier and the sibling group ID;
[0018] Status bits: The binary bits of the integer field represent the normal, abnormal, frozen, or abolished status respectively;
[0019] Partition key: serves as the basis for data distribution and query routing;
[0020] The code association table includes: ancestor code, ext code, depth, reuse version number and partition key, with the root code as the partition key;
[0021] Step 2: Perform query processing based on the reverse linked list and the code association table.
[0022] Furthermore, the partition key is calculated from the code value hash.
[0023] Furthermore, step 2 specifically involves:
[0024] Single code basic information query: Enter any code value, and the system will directly route to the corresponding segment based on its partition key to query the code information table;
[0025] Tree query:
[0026] Given a root node, query all its descendant nodes: directly query the association table;
[0027] Given a leaf node, find the complete path from it to the root node: the reverse linked list in the parsing information table;
[0028] Given an intermediate node, find the path from it to the root and all its descendant nodes: First, find the root using a reverse linked list; then, find all descendants of the root using a key association table.
[0029] Given any node, find all other nodes that share the same root: First, find the root using the reverse linked list; then, use the key association table to find all descendants of the root, excluding the node itself.
[0030] Given an intermediate node, query all its descendant nodes: First, find the root based on the reverse linked list; then, find all descendants of the root based on the key association table, and filter out the subtree of that node.
[0031] Sibling code lookup: When the input code is not the primary node, first find the primary node code in the sibling group information based on its sibling group ID, and then perform a lookup using the primary node code.
[0032] Furthermore, step 1 also includes setting up a history table to record all change history;
[0033] Step 2 also includes multi-version queries:
[0034] Default mode: Returns the latest version of the data;
[0035] Historical mode: Specify a version number to query the corresponding historical snapshot;
[0036] Full data mode: Query all historical versions of the code.
[0037] Furthermore, step 1 also includes setting up an attribute mapping table, including: attribute values, associated sample codes, and partition keys;
[0038] Step 2 also includes attribute query: calculate the partition key based on the input attribute value, query the corresponding attribute mapping table, and obtain the sample code associated with the attribute; use the sample code as input, execute the tree query process, and then filter the required data from the result set according to business rules.
[0039] Furthermore, the example code is the code for the box-lifting level.
[0040] Furthermore, step 2 also includes batch code lookup:
[0041] Step a: Grouping calculation: Group the input code list according to its respective partition key to form several partition-code list mapping pairs;
[0042] Step b: Concurrent dispatch: Use a configurable thread pool to create independent query tasks for each group and send them to the corresponding database shards simultaneously;
[0043] Step c: Parallel execution: Each database shard independently executes its own set of query tasks;
[0044] Step d: Result aggregation: The main thread waits for all concurrent tasks to complete, collects the result sets returned by each shard, merges them, and returns the query results.
[0045] Furthermore, it also includes step 3, event-driven asynchronous updates:
[0046] Master data update: Directly modify the code information table;
[0047] Event sending: Encapsulate the change event into a message, including a unique ID, event type, business primary key, and old and new values; send it to the message queue using the business primary key as the partition key;
[0048] Asynchronous consumption: Multiple consumer groups process redundant data updates in parallel, including updates to the code association table, reverse linked list, attribute mapping table, and historical record table;
[0049] Idempotent write: Deduplication based on unique key, version number condition update, or status condition update;
[0050] Stream computing: Subscribe to core event streams to perform anomaly detection, real-time statistics, and risk control interception tasks;
[0051] Eventual consistency guarantee: message queues persist events, consumers support retries, dead-letter queues require manual intervention, and periodic reconciliation corrects discrepancies;
[0052] Deassociation is handled recursively: Deassociations are recursively broken in the order of leaf code, intermediate node, and root code, and the code association table and historical record table are updated asynchronously through a message queue.
[0053] The advantages of this invention compared to the prior art are:
[0054] (1) Query performance has been greatly improved
[0055] Mechanism: The key association table pre-stores all ancestor-descendant relationships, and the reverse linked list pre-stores the complete path, so there is no need for recursion or multiple joins during queries; the partition key ensures that all queries are executed within a single partition.
[0056] Results: Single key query ≤ 20ms, tree query ≤ 2 times, query time ≤ 50ms database access, performance improvement of tens of times compared to traditional recursive query.
[0057] (2) The efficiency improvement of batch query is directly proportional to the concurrency.
[0058] Mechanism: The batch query across shards is split into multiple concurrent queries on a single shard, each shard is executed in parallel, and the main thread aggregates the results.
[0059] Effects: The serial execution time T0+T1+...+Tn is reduced to max(T0,T1,...,Tn). When the load of each shard is balanced and the concurrency is n, the query time is reduced to 1 / n of the serial execution time. The batch query of 1000 codes is reduced from seconds to hundreds of milliseconds.
[0060] (3) Database sharding and table partitioning are user-friendly and support dynamic expansion.
[0061] Mechanism: Consistent hashing partition key generation strategy ensures accurate query routing; partition key design follows the principle that "all queries must fall on a single shard".
[0062] Effect: The system can be horizontally scaled to hundreds of billions of data points. When expanding, only the ownership of some data shards needs to be recalculated, without the need to stop the service.
[0063] (4) Covers tree query across all scenarios and has strong business adaptability.
[0064] Mechanism: The code association table (top-down) and the reverse linked list (bottom-up) complement each other, and work together with the same group information to process multiple code scenarios.
[0065] Results: All tree query needs, such as root query for descendants, leaf query for path, node query for subtree, and query for other nodes with the same root, can be completed within ≤2 queries.
[0066] (5) Status integration, query and filtering combined
[0067] Mechanism: Status bit storage mode, the code of the specified status is directly filtered through bit operations during query.
[0068] Results: No need for additional association with the status table, improved query efficiency, and reduced code complexity.
[0069] (6) Support the reuse of physical objects and ensure historical traceability.
[0070] Mechanism: The version number reuse mechanism stores different usage periods of the same physical code independently; the query defaults to retrieving the latest version, and supports querying specific versions or the full set of data.
[0071] Results: In scenarios involving the reuse of pallets and boxes, query results accurately distinguish between current and historical associations, ensuring complete and traceable business data.
[0072] (7) Decoupling of data update and query, high throughput and low latency
[0073] Mechanism: Event-driven architecture, the main data is updated and returned immediately, and redundant updates are executed asynchronously; idempotent writes ensure that message duplication does not lead to data errors.
[0074] Results: Core query paths are unaffected by update operations, system throughput is improved, and response time is stable.
[0075] (8) Real-time business processing capability
[0076] Mechanism: The streaming computing engine consumes event streams in real time and performs tasks such as anomaly detection, real-time statistics, and risk control interception.
[0077] Results: Second-level alerts for error codes, real-time risk control for marketing campaigns, and real-time updates to operational data.
[0078] (9) Eventual consistency guarantee
[0079] Mechanism: Multiple mechanisms including message queue persistence, consumer-side retries, dead-letter queues, and periodic reconciliation.
[0080] Results: Asynchronous updates ensure no message loss, and ultimately all redundant data is consistent with the master data, making the system reliable. Attached Figure Description
[0081] Figure 1 Technical architecture diagram;
[0082] Figure 2 For event-driven update flowchart;
[0083] Figure 3 Flowchart for deassociation process. Detailed Implementation
[0084] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.
[0085] To address the technical challenge of rapid join queries on massive amounts of code data in a sharded database environment, this invention proposes a layered technical architecture, such as... Figure 1 As shown. The architecture is divided into three layers from bottom to top:
[0086] Storage Layer: Addresses the organization and storage challenges of massive amounts of data. It employs a horizontal sharding strategy based on partition keys. The core data model includes a code information table, a code association table, an attribute mapping table, and a historical record table. Each table uses the hash value of the business primary key as the partition key, ensuring even data distribution and allowing queries to accurately locate a single shard.
[0087] Routing Layer: Solves the problem of accurate query request location. Based on the consistent hashing algorithm, the business primary key in the query conditions is converted into a partition key, and the database middleware directly routes the query to the target physical shard, fundamentally eliminating the performance loss caused by cross-partition scans.
[0088] Execution Layer: Addresses the issue of efficient execution across diverse query scenarios. Dedicated execution paths are designed for different scenarios such as single-key queries, tree queries, attribute queries, and batch queries. Simultaneously, a streaming computing engine and message queue are introduced to achieve asynchronous decoupling and eventual consistency of data updates.
[0089] Based on this technical architecture, a fast query method for tree-like relationships in massive code data includes:
[0090] Step 1: Set up the code information table and code association table. To adapt to different query needs, you can also set up an attribute mapping table, a history table, etc. All tables are partitioned based on the partition key to ensure that related queries can be completed within a single partition.
[0091] ① Code Information Table
[0092] This table is the core storage medium for code data; each row represents complete information about a code in a specific version. The main fields are designed as follows:
[0093] Composite primary key: Consists of "code value + reused version number". Using "code value + reused version number" as a composite primary key can accurately distinguish between current and historical associations when the same physical code coexists with multiple versions, achieving complete traceability of business data.
[0094] Reuse Version Number: Solves version management issues in scenarios where physical tray codes are reused. Version number values mean: -1: Not reusable (e.g., disposable box codes); 0: Reusable but not yet used (initial state); ≥1: Number of times used, the version number increments with each reuse.
[0095] Reverse linked list: Stores the complete path identifier from the current code to the root code in JSON array format, for example, ["small box code A", "carrying box code B", "transfer box C", "pallet code D"]. This field is key to enabling fast "bottom-up" lookups.
[0096] Sibling information: includes the master node identifier (indicating whether the code is a tree node in the sibling group) and the sibling group ID (if a sibling code exists, the group identifier is recorded).
[0097] Status bits: Use binary bits of integer fields to represent statuses such as "normal", "abnormal", "frozen", and "abolish", and support quick filtering during queries through bitwise operations.
[0098] Partition key: Calculated by hashing the code value, serving as the basis for data distribution and query routing.
[0099] ② Code Association Table
[0100] This table solves the "top-down" tree query problem, storing all ancestor-descendant relationship pairs. Each row contains: ancestor code, descendant code, depth, reuse version number (distinguishing different usage periods), and partition key, with the root code serving as the partition key.
[0101] The design of using the root code as the partition key ensures that when querying all descendants of a certain root code, all related records are in the same partition.
[0102] ③ Attribute Mapping Table
[0103] This addresses the query problem based on non-code value attributes such as batch number and production order number. An independent mapping table is established for each commonly used business attribute, containing: attribute value (e.g., batch number), a list of associated code values (usually the intermediate code corresponding to that attribute), and a partition key.
[0104] ④ Historical Records Table
[0105] Record all change history (code information, associations, status changes, etc.) for traceability auditing and data recovery.
[0106] Step 2: Perform query processing based on the reverse linked list and the code association table.
[0107] By utilizing a dual path of reverse linked lists (bottom-up) and key association tables (top-down), all tree query scenarios can be covered with a maximum of two database accesses.
[0108] Single code basic information query:
[0109] Input any code value, and the system will directly route to the corresponding shard based on its partition key, query the code information table, and return information such as the current status, reverse linked list, and reuse version number of the code within 20ms.
[0110] Taking the hierarchy of pallet codes, box codes, large box codes, and small box codes in a real-world business scenario as an example, the implementation of five tree query scenarios is shown in the table below:
[0111]
[0112] Special scene handling:
[0113] Sibling code lookup: When the input code is not the primary node (such as the box code), the system first finds the primary node code in the sibling association table based on its sibling group ID, and then executes the above query process with the primary node code to ensure that the returned results include all sibling codes.
[0114] Multi-version query: Supports three query modes:
[0115] Default mode: Returns the latest version (reusing the version number with the highest value);
[0116] Historical mode: Specify a version number to query the corresponding historical snapshot;
[0117] Full data mode: Query all historical versions of the code.
[0118] Attribute query based on two locations:
[0119] For queries based on non-code value attributes such as batch number or production order number, the system employs a two-step location strategy:
[0120] First location: Calculate the partition key based on the input attribute value, query the corresponding attribute mapping table, and obtain the example code associated with the attribute (usually the code of the box level is selected as the entry point).
[0121] Second positioning: Using the example code as input, execute the tree query process (find the root of the reverse linked list → query the full list of the code association table), and then filter out the final required data from the result set according to the business rules.
[0122] This strategy ensures that each query falls within a single shard, with a total number of database accesses not exceeding 3 (1 for the attribute mapping table + a maximum of 2 for tree queries), which is far superior to the cross-partition full table scan of traditional solutions.
[0123] Grouped concurrent batch query: When the system receives a batch code query request, it adopts a grouped concurrent aggregation strategy.
[0124] Step A: Grouping Calculation: The input code list is grouped according to its respective partition key, forming several "partition-code list" mapping pairs. All codes within each group reside in the same physical partition.
[0125] Step B: Concurrent dispatch: Using a configurable thread pool, create independent query tasks for each group and send them to the corresponding database shards simultaneously.
[0126] Step C: Parallel execution: Each database shard independently executes its own set of query tasks. Since the query conditions are all within the local shard, there is no need for cross-shard data exchange.
[0127] Step D: Result Aggregation: The main thread waits for all concurrent tasks to complete (timeout can be configured), collects the result sets returned by each shard, merges them, and returns them to the caller.
[0128] This optimization strategy reduces the total time of serial queries from the sum of the times of each partition (serial time = T0 + T1 + T2) to the time of the slowest partition (concurrent time = max(T0, T1, T2)). The efficiency improvement of batch queries is proportional to the degree of concurrency.
[0129] Furthermore, it also includes event-driven data updates and consistency guarantees.
[0130] To resolve the conflict between data update and query performance in high-concurrency scenarios, this invention constructs an event-driven asynchronous update architecture, such as... Figure 2 As shown.
[0131] ① Update process design:
[0132] All data changes (status changes, association creation / destruction, attribute updates, version changes) follow the standardized process below:
[0133] Master data update: Directly modify core tables such as the basic information table of the code. This operation is a single-shard transaction, ensuring that master data is written quickly.
[0134] Event sending: Encapsulate change events into messages (containing unique ID, event type, business primary key, old and new values, etc.) and send them to a message queue (such as Kafka). Use the business primary key as the message partition key to ensure that change events with the same key are consumed in an orderly manner.
[0135] Asynchronous consumption: Multiple consumer groups subscribe to relevant topics and perform redundant data updates in parallel, including: incremental maintenance of related tables, recalculation and update of reverse linked lists, update of attribute mapping tables, and writing of historical records tables.
[0136] ② Idempotent write guarantee:
[0137] All consumer processing logic is designed to be idempotent to ensure that duplicate message delivery does not affect data integrity.
[0138] Deduplication based on unique keys: The event ID or business serial number is used as the unique key in the target table, and the "ignore if it exists" semantics are adopted when writing.
[0139] Update based on version number: The update carries the data version number, and the update is only performed if the version number matches.
[0140] Update based on state conditions: only perform changes if the current state meets expectations (e.g., only change from "normal" to "frozen").
[0141] ③ Real-time processing of streaming computing:
[0142] The streaming computing engine subscribes to the core event stream and executes real-time tasks:
[0143] Anomaly detection task: Real-time determination of whether the scanning event stream is missing a preceding step (such as no production data). If an anomaly is found, a status change event is generated.
[0144] Real-time statistics task: Count the number of scans and the anomaly rate of each code every second, and output the data to the real-time monitoring dashboard.
[0145] Risk control interception task: Real-time verification of QR code scanning rules for marketing activities (e.g., limited to once per day), and immediate return of interception results.
[0146] ④ Eventual consistency guarantee:
[0147] The message queue persistence mechanism ensures that events are not lost.
[0148] The consumer side supports a retry strategy, and failed events are entered into a dead-letter queue for manual intervention.
[0149] The periodic reconciliation task scans for consistency between master data and redundant data, and automatically repairs any discrepancies found.
[0150] ⑤ Recursive processing of deassociation:
[0151] When it is necessary to unlink the code, the system recursively processes the request according to the following rules: Figure 3 As shown:
[0152] Leaf code removal: Delete the association with the parent, clear production information and reset the status to inactive; if the parent has no other subordinates, recursively remove the parent from the parent.
[0153] Intermediate node removal: First, recursively remove all descendants, then remove the association between itself and its parent.
[0154] Root code removal: Removes all associations in the entire tree and resets the status of all codes to inactive.
[0155] All release operations are asynchronously updated via message queues to the association table and historical record table, ensuring a fast response from the main process.
[0156] Example 1: Physical pallet code lookup associated with turnover boxes (correspondence tree lookup + multi-version lookup)
[0157] This embodiment demonstrates the tree query process in a physical object reuse scenario, fully showcasing the collaborative operation of reverse linked lists, code association tables, and version number management.
[0158] Scenario Description: A factory uses reusable physical pallets, each printed with a unique code SW_M_1. The pallet has been used twice: first associated with virtual code XN_M_1, and second with virtual code XN_M_2. Now, a user scans the pallet code to query the currently associated reusable container.
[0159] Execution process:
[0160] (1) Input recognition: The user scans the physical pallet code SW_M_1, and the system recognizes it as a physical pallet code based on the code type field.
[0161] (2) Version Acquisition: Query the code information table based on the code value SW_M_1. Since the table uses "code value + reuse version number" as a composite primary key, it returns all version records. The system defaults to taking the record with the largest reuse version number (the latest version), and obtains the currently associated virtual key XN_M_2.
[0162] (3) Reverse Linked List Parsing: Query the code information table of virtual key XN_M_2 and obtain its reverse linked list field (JSON array).
[0163] (4) Parse to obtain the complete path: ["XN_M_2", "transfer box C", "tuo code A"], and extract the root code "tuo code A" from it.
[0164] (5) Root code partition calculation: Calculate the partition key based on the root code A to determine the physical partition in which it is located.
[0165] (6) Code association table query: Query all records in the code association table where the ancestor is Thoth code A (within the same slice). Return all descendant records of Thoth code A, each record containing the surrogate code, depth, and reuse version number.
[0166] (7) Subtree Filtering: Filter records whose descendant nodes belong to the XN_M_2 subtree (with a depth greater than the depth of XN_M_2) from the results, i.e., all turnover box codes. At the same time, determine the status of the physical pallet code by comparing the current version with the latest version: if the current query version is consistent with the latest version, the status is "Associating"; otherwise, it is "Removed".
[0167] (8) Result return: Returns the list of turnover box codes and the status of physical pallet codes.
[0168] Technical effect: The entire process involves two database accesses (code information table + code association table), both executed within a single shard, taking no more than 50ms, and accurately distinguishing between current and historical associations.
[0169] Example 2: Query all small box codes by batch number (corresponding attribute query + two location operations)
[0170] This example demonstrates the complete process of associating with a business attribute (batch number) query code.
[0171] Scenario Description: Quality management personnel need to trace all the small box codes produced by batch number BATCH2025001 in order to conduct quality re-inspection.
[0172] Execution process:
[0173] (1) Input: Enter batch number BATCH2025001.
[0174] (2) First location (attribute mapping table query): Calculate the partition key based on the batch number and query the "batch number-code mapping table" (a type of attribute mapping table). Obtain a list of all delivery codes associated with this batch, for example, [TX001, TX002, TX003]. The delivery code, as an intermediate layer node, is the entry point for the query.
[0175] (3) Second location (perform tree query for each box pick-up code):
[0176] Taking the first carry-on code TX001 as an example:
[0177] Calculate the partition key based on the baggage code TX001, and retrieve all descendants of this baggage code from the code association table.
[0178] The records at the depth of the small box codes are selected from the descendants, that is, all the small box codes under this box.
[0179] Perform the above steps concurrently on TX002 and TX003.
[0180] (4) Result aggregation: merge all the small box codes obtained from the box code query, and after deduplication, form the complete set of small box codes for this batch.
[0181] (5) Result return: Returns a list of all small box codes for batch BATCH2025001 and the current status of each small box code.
[0182] Technical results: Total number of queries = 1 (attribute mapping table) + N (number of box pickup codes) tree queries. Each tree query is a single-shard operation, and the overall response time is determined by the slowest single query, max(t1, t2, ..., tN), which is more than 90% more efficient than traditional cross-partition full table scans (which may take several seconds).
[0183] Example 3: Batch query of the status of 1000 codes (corresponding to concurrent group queries)
[0184] This example demonstrates the specific implementation of a batch query optimization strategy.
[0185] Scenario Description: Operations personnel need to query the current status of 1000 anti-counterfeiting codes for data analysis. These 1000 codes are distributed across 4 database shards.
[0186] Execution process:
[0187] (1) Input: code list [code1, code2, ..., code1000].
[0188] (2) Group calculation:
[0189] Calculate the partition key for each code (derived from the hash of the code value). Group the 1000 codes according to the partition key, for example, resulting in: Group 0 (shard 0): 250 codes, Group 1 (shard 1): 250 codes, Group 2 (shard 2): 250 codes, Group 3 (shard 3): 250 codes.
[0190] (3) Concurrent dispatch: The system thread pool creates 4 query tasks, each corresponding to 4 groups. Each task carries the code list of its group and the corresponding partition key, and sends query requests to the 4 shards at the same time.
[0191] (4) Parallel execution:
[0192] The execution of SELECT * FROM code_base WHERE code IN (the code list of group 0) AND partition_key=0 in partition 0 takes 30ms.
[0193] A similar query was executed on shard 1, taking 40ms. A similar query was executed on shard 2, taking 35ms. A similar query was executed on shard 3, taking 45ms.
[0194] Each partition query is completely independent and does not interfere with the others.
[0195] (5) Result aggregation: The main thread waits for all tasks to complete (actually waiting for the slowest shard 3, about 45ms). Collect the result sets returned by the 4 shards and merge them into a complete status information of 1000 codes.
[0196] (6) Result return: Returns a list containing 1000 code states.
[0197] Comparative analysis:
[0198] If executed serially: Total execution time = 30 + 40 + 35 + 45 = 150ms;
[0199] After concurrent execution: Total execution time = max(30,40,35,45) = 45ms;
[0200] Efficiency improvement: Estimation time reduced to 30% of serial processing time, and throughput increased by 3.3 times.
[0201] Example 4: De-associate intermediate nodes (corresponding to recursive de-association processing)
[0202] This embodiment demonstrates the recursive processing flow of unlinking the lockbox code D from its parent level, showcasing the complexity of the unlinking operation and the asynchronous update mechanism within a tree structure.
[0203] Scenario Description: During production, an incorrect association was found with the pick-up box code D, requiring it to be unlinked from the upstream transit box C. Pick-up box code D is already associated with 10 smaller box codes.
[0204] Execution process:
[0205] (1) Input: Disassociation request, specified code value = box code D, parent = transit box C.
[0206] (2) Node type judgment: Query the code information table of box code D, obtain its reverse linked list, and determine that it has a lower-level node (small box code), so it is an intermediate node.
[0207] (3) Recursively process descendants (synchronously executed): Query the code association table to obtain all descendant records (i.e., 10 small box codes) where the ancestor node = box code D. Perform the leaf removal process for each small box code:
[0208] a. Delete the association between the small box code and the delivery box code D (delete the relevant record from the code association table).
[0209] b. Clear the production information field of the small box code.
[0210] c. Reset the status of the small box code to "inactive".
[0211] d. Write the change information of the small box code to the history table (asynchronously via message queue).
[0212] (4) Remove self-association:
[0213] Delete the association between container pick-up code D and transit container C (delete the record with ancestor node = C and descendant node = D from the code association table). Clear the production information for container pick-up code D. Reset the status of container pick-up code D to "inactive".
[0214] (5) Check the parent status: Query whether the transfer box C has any other subordinates (other box codes). Query the record with ancestor node = C and depth = 1 through the code association table. If there are other subordinates, the process terminates; if there are no other subordinates, recursively remove the association between the transfer box C and the parent (repeat steps (3)-(5)).
[0215] (6) Asynchronous update: Encapsulate all changes involved in this release operation (box code B, 10 small box codes) as events and send them to the message queue. Consumers subscribe to messages and asynchronously update: code association table (already deleted synchronously, but consistency needs to be confirmed), historical record table (written with complete operation log), and reverse linked list (recalculate the path of the affected codes).
[0216] (7) Result return: The main process returns a success message within 50ms, and the asynchronous update is executed in the background.
[0217] Final state:
[0218] The box code D and all its subordinate box codes have been de-associated, and their status has been reset to "inactive".
[0219] If transfer box C has no other subordinate units, it will also be deactivated; otherwise, it will be retained.
[0220] All historical changes can be traced through the historical record table.
Claims
1. A method for fast query of tree-structure association in mass code data, characterized in that, include: Step 1: Set up the code information table and code association table. All tables are partitioned based on the partition key. The code information table includes: Composite primary key: Consists of a key value and a reused version number; Reuse version number: Used to identify reuse information; Reverse linked list: Stores the complete path identifier from the current code to the root code in JSON array format; Sibling information: includes the master node identifier and the sibling group ID; Status bits: The binary bits of the integer field represent the normal, abnormal, frozen, or abolished status respectively; Partition key: serves as the basis for data distribution and query routing; The code association table includes: ancestor code, ext code, depth, reuse version number and partition key, with the root code as the partition key; Step 2: Perform query processing based on the reverse linked list and the code association table.
2. The method of claim 1, wherein, The partition key is calculated from the hash of the code value.
3. The method of claim 1, wherein, Step 2 is as follows: Single code basic information query: Enter any code value, and the system will directly route to the corresponding segment based on its partition key to query the code information table; Tree query: Given a root node, query all its descendant nodes: directly query the association table; Given a leaf node, find the complete path from it to the root node: the reverse linked list in the parsing information table; Given an intermediate node, find the path from it to the root and all its descendant nodes: First, find the root using a reverse linked list; then, find all descendants of the root using a key association table. Given any node, find all other nodes that share the same root: First, find the root using the reverse linked list; then, use the key association table to find all descendants of the root, excluding the node itself. Given an intermediate node, query all its descendant nodes: First, find the root based on the reverse linked list; then, find all descendants of the root based on the key association table, and filter out the subtree of that node. Sibling code lookup: When the input code is not the primary node, first find the primary node code in the sibling group information based on its sibling group ID, and then perform a lookup using the primary node code.
4. The method of claim 3, wherein, Step 1 also includes setting up a history table to record all change history; Step 2 also includes multi-version queries: Default mode: Returns the latest version of the data; Historical mode: Specify a version number to query the corresponding historical snapshot; Full data mode: Query all historical versions of the code.
5. The method of claim 4, wherein, Step 1 also includes setting up an attribute mapping table, including: attribute values, associated sample codes, and partition keys; Step 2 also includes attribute query: calculate the partition key based on the input attribute value, query the corresponding attribute mapping table, and obtain the sample code associated with the attribute; use the sample code as input, execute the tree query process, and then filter the required data from the result set according to business rules.
6. The method of claim 5, wherein, The example code is the code for the box-carrying level.
7. The method of claim 5, wherein the method further comprises: Step 2 also includes batch code lookup: Step a: Grouping calculation: Group the input code list according to its respective partition key to form several partition-code list mapping pairs; Step b: Concurrent dispatch: Use a configurable thread pool to create independent query tasks for each group and send them to the corresponding database shards simultaneously; Step c: Parallel execution: Each database shard independently executes its own set of query tasks; Step d: Result aggregation: The main thread waits for all concurrent tasks to complete, collects the result sets returned by each shard, merges them, and returns the query results.
8. The method for fast querying of tree-like relationships in massive code data according to claim 7, characterized in that, It also includes step 3, event-driven asynchronous updates: Master data update: Directly modify the code information table; Event sending: Encapsulate the change event into a message, including a unique ID, event type, business primary key, and old and new values; send it to the message queue using the business primary key as the partition key; Asynchronous consumption: Multiple consumer groups process redundant data updates in parallel, including updates to the code association table, reverse linked list, attribute mapping table, and historical record table; Idempotent write: Deduplication based on unique key, version number condition update, or status condition update; Stream computing: Subscribe to core event streams to perform anomaly detection, real-time statistics, and risk control interception tasks; Eventual consistency guarantee: message queues persist events, consumers support retries, dead-letter queues require manual intervention, and periodic reconciliation corrects discrepancies; Deassociation is handled recursively: Deassociations are recursively broken in the order of leaf code, intermediate node, and root code, and the code association table and historical record table are updated asynchronously through a message queue.
