Tree data query optimization method and system based on hierarchical sequence identifier

Abstract

The invention discloses a tree data query optimization method and system based on hierarchical sequence identifiers. The method comprises the following steps: data storage: inputting to-be-stored data by a user, generating an HSI code for the to-be-stored data, storing the HSI code into a tree structure data table with an hsicode field, and constructing a corresponding data structure tree; data query: a user initiates data query and inputs to-be-queried data; constructing a select query statement, and performing prefix matching query based on the constructed select statement; and returning query data information. According to the method, the prefix coding rule corresponding to the HSI is constructed, when data query is carried out, the database can be completed only through one-time efficient index range query, the pressure of the database is greatly relieved, the response speed is increased by a plurality of orders of magnitude, and compared with path enumeration using separators, the HSI has no redundant characters, the storage space is more compact, and the index efficiency is higher.

Description

Technical Field

[0001] This invention relates to the field of data retrieval technology, specifically to a tree-structured data query optimization method and system based on hierarchical sequence identifiers. Background Technology

[0002] In the field of database management, the storage and retrieval of tree-structured data typically employs the following two mainstream solutions, but both have significant drawbacks: 1. Parent Key Identification Method (Adjacency List) This approach records parent nodes by setting a `parent_id` field in the table. Its advantage lies in the simplicity of inserting and deleting nodes. However, its fatal flaw is extremely poor subtree query performance. Since descendant nodes cannot be directly known, recursive queries (such as SQL WITH RECURSIVE) must be relied upon. Recursion incurs significant performance overhead as the hierarchy deepens, leading to slow response times and becoming a system bottleneck in cases of large data volumes or deep hierarchies. This inefficient query problem is the primary drawback that this invention aims to overcome.

[0003] 2. Path Enumeration This approach records the node's path by adding a `path` field (e.g., ` / root / id1 / id2 / `). It queries the subtree by matching string prefixes, avoiding recursion and improving query efficiency. However, its drawback is the high maintenance cost. The path field typically consists of a variable-length string and delimiters, and its structure is parsed. Once a node is moved, the entire path string of all its descendant nodes must be parsed and updated. This operation is cumbersome, error-prone, and locks a large amount of data during transactions, severely impacting database write performance.

[0004] In summary, existing technologies struggle to strike a balance between read / write performance and maintenance costs. Therefore, there is an urgent need for a new solution that can simultaneously achieve efficient subtree querying and convenient maintenance. Summary of the Invention

[0005] To address the aforementioned issues, this invention provides a tree-structured data query optimization method based on hierarchical sequence identifiers, comprising the following steps: Data storage: The user inputs data to be stored, the data to be stored is generated into an HSI code, and then stored in a tree-structured data table with an hsi_code field to construct the corresponding data structure tree; Data Query: The user initiates a data query and enters the data to be queried; a select query statement is constructed, and a prefix matching query is performed based on the constructed select statement; the queried data information is returned.

[0006] Furthermore, the HSI encoding is specifically composed of global placeholders and hierarchically increasing sequence numbers of multiple fixed bits; Specifically, the global placeholder is a short number character located at the beginning of the HSI, used to uniquely identify the tree structure data table and prevent HSI conflicts between different trees; The hierarchical incrementing sequence number is specifically defined as follows: within each level, each node is assigned a unique and sequentially increasing sequence number of a fixed number of digits.

[0007] Furthermore, the specific rules for generating the HSI encoding are as follows: Root node: Root node HSI = global placeholder + first-level incrementing sequence number; Non-root node: The HSI of a non-root node = the complete HSI of its parent node + the incrementing sequence number of the next level.

[0008] Furthermore, the select query statement is specifically as follows: SELECT ... WHERE hsi_code>= 'Current node HSI' AND hsi_code<'Next sibling node HSI of the current node'; Alternatively: SELECT ... WHERE hsi_code LIKE 'current node HSI%'.

[0009] Furthermore, when the hierarchical structure in the data structure tree changes, a new HSI code is generated for the data to be changed, and the hsi_code field of the data to be changed and all its descendant nodes is updated. When a node in the data structure tree is written to frequently, the node data to be deleted is marked as invalid, a node with a new HSI code is inserted at a new position, and its subtree structure is gradually reconstructed.

[0010] A tree-structured data query system based on hierarchical sequence identifiers includes an HSI generation module, a data query module, a structure maintenance module, and a data storage module; The HSI generation module is used to generate corresponding HSI codes for newly added nodes; The data query module is used to receive user query requests and perform data queries based on the hsi_code field; The structure maintenance module is used to coordinate the update of the HSI when a node moves; The data storage module is used to store a tree-structured data table with an hsi_code field.

[0011] Furthermore, it also includes an API interface module, which is used to receive data requests from user clients and transmit the queried data to the user clients.

[0012] This invention provides a tree-based data query optimization method and system based on hierarchical sequence identifiers, which has the following beneficial effects: This invention constructs a prefix encoding rule corresponding to HSI, so that when querying data, the database only needs one efficient index range query to complete the query, which greatly reduces the pressure on the database and improves the response speed by several orders of magnitude. Compared with path enumeration using delimiters, HSI has no redundant characters, more compact storage space, and higher indexing efficiency. Attached Figure Description

[0013] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0014] Figure 1 This is a schematic diagram of HSI encoding generation provided by the present invention; Figure 2 This is a schematic diagram of the system structure provided by the present invention. Detailed Implementation

[0015] It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of the invention.

[0016] The following detailed description of the implementation method of the present invention is in conjunction with the accompanying drawings. The description is only a partial embodiment and not all embodiments. For clarity, representations and descriptions unrelated to the present invention are omitted in the drawings and description.

[0017] To provide a clearer understanding of the technical features, objectives, and beneficial effects of this invention, the following detailed description of the technical solution is provided. Obviously, the described embodiments are only a portion of the embodiments of this invention, not all of them, and should not be construed as limiting the scope of implementation of this invention. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without inventive effort are within the protection scope of this invention.

[0018] This invention provides a tree-structured data query optimization method based on hierarchical sequence identifiers, comprising the following steps: Data storage: The user inputs the data to be stored, the data to be stored generates HSI encoding, and then stores it in a tree-structured data table with an hsi_code field to construct the corresponding data structure tree; Data Query: The user initiates a data query and enters the data to be queried; a select query statement is constructed, and a prefix matching query is performed based on the constructed select statement; the queried data information is returned.

[0019] HSI encoding is specifically composed of a global placeholder and a multi-level, fixed-length (e.g., 4-digit) hierarchically increasing sequence number. The global placeholder is a short digit at the very beginning of the HSI, used to uniquely identify the tree-structured data table and prevent HSI conflicts between different trees. The hierarchically increasing sequence number is assigned to each node at each level as a unique, sequentially increasing sequence number of fixed digits. The sequence number is padded with leading zeros if the length is insufficient (e.g., 0001, 0002, ...). This fixed-length design is the foundation for subsequent efficient prefix matching and batch updates.

[0020] The specific rules for generating HSI codes are as follows: Figure 1 As shown: Root node: Root node HSI = global placeholder + first-level incrementing sequence number; Non-root node: The HSI of a non-root node = the complete HSI of its parent node + the incrementing sequence number of the next level.

[0021] The specific SELECT query statement is as follows: SELECT ... WHERE hsi_code>= 'Current node HSI' AND hsi_code<'Next sibling node HSI of the current node'; Alternatively: SELECT ... WHERE hsi_code LIKE 'current node HSI%'.

[0022] Fixed-length encoding ensures that the sequence number at each level is of a fixed length, while the continuity of the numerical sequence number guarantees that the sequence number is continuously increasing. Therefore, the "next value" can be easily obtained by calculating the current value + 1 without any additional overhead. The database engine directly locates the first value in the index that is greater than or equal to, for example, 100010002, and then reads sequentially along the linked list of the leaf nodes. Once it encounters the first value greater than or equal to 100010003, it immediately stops scanning and returns all scanned rows. This process is completely efficient, precisely scanning the target range.

[0023] When the structure of each level in the data structure tree changes, a new HSI code is generated for the data to be changed, and the hsi_code field of the data to be changed and all its descendant nodes is updated. When a node in the data structure tree is written to frequently, the node data to be deleted is marked as invalid, a node with a new HSI code is inserted at a new position, and its subtree structure is gradually reconstructed.

[0024] A tree-structured data query system based on hierarchical sequence identifiers, such as Figure 2 As shown, it includes an HSI generation module, a data query module, a structure maintenance module, and a data storage module; The HSI generation module is used to generate corresponding HSI codes for newly added nodes; The data query module is used to receive user query requests and perform data queries based on the hsi_code field; The structure maintenance module is used to coordinate the update of the HSI when a node moves; The data storage module is used to store a tree-structured data table with an hsi_code field.

[0025] It also includes an API interface module, which is used to receive data requests from user clients and transmit the queried data to the user clients.

[0026] Example 1: Initial construction of the department tree structure: Suppose a company needs to manage its organizational structure through information technology, which is a typical tree structure.

[0027] Create a table named "department" with the structure shown in Table 1 below: Table 1 Department Table field name type constraint illustrate id BIGINT PRIMARY KEY, AUTO_INCREMENT Primary key, unique identifier name VARCHAR(50) NOT NULL Department Name hsi_code VARCHAR(255) NOT NULL, UNIQUE INDEX Hierarchical sequence identifiers (HSIs) must be indexed. parent_id BIGINT DEFAULT NULL Parent department ID (optional, used for auxiliary operations or compatibility with legacy logic) Set the HSI encoding generation rules: Global placeholder (GLOBAL_PLACEHOLDER): "1"; Length of each level of sequence number (SERIAL_LENGTH): 4.

[0028] Constructing a departmental tree: Assume the company's initial departmental structure is as follows: the head office has a marketing department and a research and development department; the research and development department has a front-end team and a back-end team.

[0029] Insert the root node (Headquarters): This is a first-level node with no parent node. The current maximum sequence number is 0, and the new sequence number is 1. Generate HSI: GLOBAL_PLACEHOLDER + "0001" = "10001"; SQL: INSERT INTO department(name, hsi_code) VALUES ('Headquarters', '10001'); After insertion, the record's id is 1.

[0030] Insert first-level child nodes (Marketing Department, R&D Department): Their parent node is 10001 (Headquarters).

[0031] Marketing Department: Parent node HSI is 10001, the current maximum sequence number under the parent node is 0, and the new sequence number is 1. Generate HSI: 10001 + "0001" = "100010001"; SQL: INSERT INTO department (name, hsi_code,parent_id) VALUES ('Marketing Department', '100010001', 1).

[0032] R&D Department: Parent node HSI is 10001, the current maximum sequence number under the parent node is 1 (Marketing Department), and the new sequence number is 2. Generate HSI: 10001 + "0002" = "100010002"; SQL: INSERT INTO department (name, hsi_code, parent_id) VALUES ('R&D Department', '100010002', 1); Insert second-level child nodes (front-end group, back-end group): their parent node is 100010002 (R&D Department).

[0033] Front-end Group: Parent node HSI is 100010002, the current maximum sequence number under the parent node is 0, and the new sequence number is 1. Generate HSI: 100010002 + "0001" = "1000100020001"; SQL: INSERT INTO department (name,hsi_code, parent_id) VALUES ('Front-end Group', '1000100020001', {R&D Department ID}).

[0034] Backend Group: Parent node HSI is 100010002, the current maximum sequence number under the parent node is 1 (frontend group), and the new sequence number is 2. Generate HSI: 100010002 + "0002" = "1000100020002"; SQL: INSERT INTO department(name, hsi_code, parent_id) VALUES ('Backend Group', '1000100020002', {R&D Department ID}).

[0035] At this point, the initial department tree construction is complete. The data at this stage is shown in Table 2 below: Table 2 Query the R&D department and all its subordinate departments: The R&D department's his_code is 100010002; according to the HIS rules, the next his_code at the same level as the R&D department is 100010003; at this time, the SQL uses a more efficient range query method: SELECT * FROM department WHERE hsi_code>= '100010002' and his_code<'100010003'; this query will immediately return three records: R&D department (100010002), front-end group (1000100020001), and back-end group (1000100020002), without any recursion.

[0036] Example 2: Departmental Tree Structure Adjustment (Node Movement): The company has now decided to move the "Backend Group" from the "R&D Department" to the "Marketing Department".

[0037] 1. Determine the new HSI prefix: The HSI of the target parent node (Marketing Department) is 100010001. Query the largest current HSI sequence number under Marketing Department: SELECT MAX(hsi_code) FROM department WHERE hsi_code LIKE '100010001____'; (Assuming it returns 1000100010000, meaning there are no sub-departments yet). Therefore, "Backend Group," as the first sub-department of Marketing Department, should obtain sequence number 0001. The new HSI prefix is: 100010001 + 0001 = 1000100010001.

[0038] 2. Perform a batch update: This requires updating the "backend group" and all its descendant nodes (in this example, it has no descendants). This can be done with a single SQL statement: `UPDATE department SET hsi_code = REPLACE(hsi_code, '1000100020002', '1000100010001') WHERE hsi_code>= '1000100020002' AND hsi_code<'1000100020003';` This operation replaces 1000100020002 (the old HSI) with 1000100010001 (the new HSI).

[0039] 3. Update Parent ID (Optional): To ensure data consistency, the `parent_id` field can be updated synchronously. SQL: `UPDATE department SET parent_id = {Marketing Department ID} WHERE id = {Backend Group ID};` After adjustment, query the marketing department subtree again: SQL: SELECT * FROM department WHERE hsi_code>= '100010001' AND his_code<'100010002'.

[0040] This will return two records: Marketing Department (100010001) and Backend Group (1000100010001). This indicates that the node move was successful and the query function is working perfectly.

[0041] The above embodiments fully demonstrate the entire process of the present invention from tree structure construction to dynamic maintenance, and verify that while significantly improving query performance, it can also efficiently handle structural changes through batch operations.

[0042] This invention constructs a prefix encoding rule corresponding to HSI, so that when querying data, the database only needs one efficient index range query to complete the query, which greatly reduces the pressure on the database and improves the response speed by several orders of magnitude. Compared with path enumeration using delimiters, HSI has no redundant characters, more compact storage space, and higher indexing efficiency.

[0043] The above description is merely a preferred embodiment of the present invention. It should be understood that the present invention is not limited to the forms disclosed herein and should not be construed as excluding other embodiments. It can be used in various other combinations, modifications, and environments, and can be altered within the scope of the concept described herein through the above teachings or related technologies or knowledge. Modifications and variations made by those skilled in the art that do not depart from the spirit and scope of the present invention should be within the protection scope of the appended claims.

Claims

1. A tree-structured data query optimization method based on hierarchical sequence identifiers, characterized in that, Includes the following steps: Data storage: The user inputs the data to be stored, generates HSI encoding for the data, and stores it in a tree-structured data table with an hsi_code field to construct the corresponding data structure tree. Data Query: The user initiates a data query and enters the data to be queried; a select query statement is constructed, and a prefix matching query is performed based on the constructed select statement; the query data information is returned.

2. The tree-based data query optimization method based on hierarchical sequence identifiers according to claim 1, characterized in that, The HSI encoding is specifically composed of global placeholders and hierarchically increasing sequence numbers of multiple fixed bits. Specifically, the global placeholder is a short number character located at the beginning of the HSI, used to uniquely identify the tree structure data table and prevent HSI conflicts between different trees; The hierarchical incrementing sequence number is specifically defined as follows: within each level, each node is assigned a unique and sequentially increasing sequence number of a fixed number of digits.

3. The tree-based data query optimization method based on hierarchical sequence identifiers according to claim 2, characterized in that, The specific rules for generating the HSI encoding are as follows: Root node: Root node HSI = global placeholder + first-level incrementing sequence number; Non-root node: The HSI of a non-root node = the complete HSI of its parent node + the incrementing sequence number of the next level.

4. The tree-based data query optimization method based on hierarchical sequence identifiers according to claim 1, characterized in that, The select query statement is specifically as follows: SELECT ... WHERE hsi_code >= 'Current node HSI' AND hsi_code < 'Next sibling node HSI of the current node'; or: SELECT ... WHERE hsi_code LIKE 'current node HSI%'.

5. The tree-based data query optimization method based on hierarchical sequence identifiers according to claim 1, characterized in that, When the structure of each level in the data structure tree changes, a new HSI code is generated for the data to be changed, and the hsi_code field of the data to be changed and all its descendant nodes is updated. When a node in the data structure tree is written to frequently, the node data to be deleted is marked as invalid, a node with a new HSI code is inserted at a new position, and its subtree structure is gradually reconstructed.

6. A tree-structured data query system based on hierarchical sequence identifiers, used to implement the tree-structured data query optimization method based on hierarchical sequence identifiers as described in any one of claims 1-5, characterized in that, It includes an HSI generation module, a data query module, a structure maintenance module, and a data storage module; The HSI generation module is used to generate corresponding HSI codes for newly added nodes; The data query module is used to receive user query requests and perform data queries based on the hsi_code field; The structure maintenance module is used to coordinate the update of the HSI when a node moves; The data storage module is used to store a tree-structured data table with an hsi_code field.

7. The tree-based data query system based on hierarchical sequence identifiers according to claim 6, characterized in that, It also includes an API interface module, which is used to receive data requests from user clients and transmit the queried data to the user clients.

Images