Database indexing method and structure based on time zone offset value

Abstract

The invention provides a database indexing method and structure based on time zone offset values, comprising a plurality of time zone offset value classification nodes, each node corresponding to a specific time zone offset value, data stored under each time zone offset value classification node being sorted according to absolute values of time data; the time zone offset value classification nodes are generated in advance according to a global time zone offset value range, and each node is set and stored in an index part of a database when the database is initialized. The database index structure based on the time zone deviation value and the implementation method of the database index structure have the beneficial effects that the time zone data query efficiency is improved, the time zone data processing complexity of the database is reduced, and the database index structure is suitable for time zone data processing in a global range and has good expansibility and maintainability.

Description

Technical Field

[0001] The invention belongs to the technical field of databases, and in particular relates to a database indexing method and structure based on time zone offset values. Background Art

[0002] Most existing database indexing technologies index the absolute value of time data, but the query efficiency is low when processing time data involving time zones. Especially in global business scenarios, the demand for processing time zone data is increasing, and existing indexing technologies can no longer meet the needs of efficient queries. Summary of the invention

[0003] In view of this, the present invention aims to propose a database indexing method and structure based on time zone offset values ​​to at least solve one of the problems in the background technology.

[0004] To achieve the above object, the technical solution of the present invention is achieved as follows: A database indexing method based on a time zone offset value, comprising: When the time zone information in the database changes, the affected time zone offset value classification node is first identified, and the time zone offset values ​​of all data items in the node are recalculated; Migrate the recalculated data items from the original time zone offset value classification node to the new time zone offset value classification node; After migrating the data item, release the original time zone offset value classification node and decide whether to keep or delete the node as needed; The migration process uses a shadow node cluster and a dual-write mechanism: a) Create a temporary shadow node with the new offset value as the key; b) Synchronously update the original node and shadow node; c) Verify data consistency through CRC32; d) Atomically switch node pointers during the transaction commit phase.

[0005] Further, the time zone information is changed to an offset value change caused by a daylight saving time adjustment or a change in time zone rules; For large-scale migration, batch pipeline processing is adopted, and each batch contains a preset number of data blocks; During the migration process, the original node remains in a read-only state, and the newly written data is directly directed to the shadow node.

[0006] Further, including updating the synchronization method: After executing the data update operation, recalculate the time zone offset value of the data, and update the time zone offset value classification node of the data item according to the calculation result; If the new time zone offset value is different from the original time zone offset value, the data item is migrated from the original time zone offset value classification node to the new time zone offset value classification node; Update the database index structure to ensure data consistency; Establish a time zone change event monitoring queue to capture time zone rule change events; adopt an incremental data repartitioning strategy to generate migration data fingerprints and establish a rollback log.

[0007] Further, the data updating operation includes updating a time field in a database table; Build a time zone-aware query optimizer to convert original query statements into standardized UTC expressions and generate distributed merge index scan plans for range queries involving multiple time zones.

[0008] Further, including: Analyze the usage of indexes and identify hot and non-hot data; Optimize cache of hot data to reduce query time; Compress and store non-hotspot data to reduce storage space usage; Detect and handle index fragmentation, and optimize the index structure by merging, reorganizing, or rebalancing nodes classified by time zone offset values; Wherein, the merging, reorganizing or rebalancing the time zone offset value classification nodes improves query efficiency by adjusting the load distribution of the nodes; The compression storage adopts an improved Delta coding algorithm to store time differences instead of absolute values; The time zone metadata dictionary is embedded in the compressed data block header, which contains all the time zone conversion rules of the node.

[0009] On the other hand, the present invention provides a database index structure based on a time zone offset value, including a plurality of time zone offset value classification nodes, each node corresponding to a specific time zone offset value, and the data stored under each time zone offset value classification node is sorted according to the absolute value of the time data; The time zone offset value classification nodes are pre-generated according to the global time zone offset value range, and each node is set and stored in the index part of the database when the database is initialized; The structure also includes a dynamic time zone offset value mapping module, which detects daylight saving time adjustments and time zone rule changes in real time and generates an offset value increment table; Each classification node stores sub-interval data with time zone offset values ​​in the range of [-12h, +14h] and with 15 minutes as the minimum granularity unit. Cross-time zone associations are established between nodes through a skip table structure.

[0010] Further, including: When the database is initialized, a time zone offset value classification node is pre-generated according to the global time zone offset value range and stored in the index part of the database; When inserting data, the time zone offset value is obtained by calculating the time field and time zone information of the data, and the data is stored in the corresponding time zone offset value classification node; When deleting data, locate the corresponding time zone offset value classification node by calculating the time field and time zone information of the data, and delete the data item from the node; When querying data, quickly locate the corresponding time zone offset value classification node based on the time zone information in the query conditions; The time zone offset value classification node adopts a multi-level hybrid index layer, which is composed of a B+ tree primary index and a hash mapping relationship table, wherein the hash table stores adjacent time zone conversion functions f(x)=UTC_time+offset_x; Each node sets a capacity threshold monitor. When the data volume exceeds the threshold, horizontal sharding is triggered to generate a chain of child nodes with the same offset value.

[0011] Furthermore, the query data operation directly accesses the relevant time data item by finding the index address of the time zone offset value classification node; The horizontal sharding uses a time sliding window algorithm to divide the node data into multiple sub-nodes, and each sub-node maintains an independent Bloom filter to quickly exclude non-query time intervals; During the sharding process, the virtual index pointer of the parent node is retained to form a hierarchical index topology.

[0012] On the other hand, the present invention provides a server comprising at least one processor and a memory communicatively connected to the processor, wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the processor to enable the at least one processor to execute any one of the database indexing methods based on time zone offset values.

[0013] On the other hand, the present invention provides a computer-readable storage medium storing a computer program, wherein the computer program, when executed by a processor, implements the above-mentioned database indexing method based on time zone offset value.

[0014] Compared with the prior art, the database index structure based on time zone offset value and its implementation method described in the present invention have the following beneficial effects: (1) The database index structure based on time zone offset value and its implementation method and structure described in the present invention improve the efficiency of time zone data query, reduce the complexity of the database in processing time zone data, are suitable for time zone data processing on a global scale, and have good scalability and maintainability; (2) The database index structure based on time zone offset value and its implementation method and structure described in the present invention realize 15-minute granularity time zone coverage (supporting 576 time zone intervals) and second-level rule change response through a dynamic time zone mapping module, and improve the efficiency of cross-time zone queries by combining hybrid indexes (B+ tree + hash table). At the same time, the shadow node double-write mechanism ensures zero perception of time zone migration, and cooperates with the time zone-aware optimizer to reduce the time consumption of complex query parsing. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] The accompanying drawings constituting a part of the present invention are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the accompanying drawings: Figure 1 Schematic diagram of a method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0016] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0017] The present invention will be described in detail below with reference to the accompanying drawings and in conjunction with embodiments.

[0018] On the one hand, if Figure 1 As shown, the present invention provides a database index structure based on a time zone offset value, which is as follows: 1. Index structure design: The index structure contains multiple time zone offset value classification nodes, each node corresponds to a time zone offset value; under each time zone offset value classification node, the time data is sorted and stored according to the absolute value; the index structure supports fast insertion, deletion and query operations.

[0019] 2. Index construction method: When the database is initialized, a time zone offset value classification node is pre-generated according to the global time zone offset value range; when inserting data, the time zone offset value is calculated according to the time field and time zone information of the data, and the data is stored in the corresponding time zone offset value classification node; when deleting data, the corresponding time zone offset value classification node is located according to the time field and time zone information of the data, and the data is deleted; when querying data, the corresponding time zone offset value classification node is quickly located according to the time zone information in the query conditions to improve query efficiency.

[0020] 3. Index update strategy: 3.1 Time zone change processing: When the time zone information in the database changes (for example, due to daylight saving time adjustment or other reasons), the index structure needs to be updated accordingly; the update strategy includes identifying the affected time zone offset value classification nodes, recalculating the time zone offset values ​​of the data in these nodes, and migrating them to the new time zone offset value classification nodes.

[0021] 3.2 Data update synchronization: When the time data in the database is updated, the index structure needs to be updated synchronously to maintain data consistency. The update strategy includes recalculating the time zone offset value based on the new time field and time zone information immediately after the data update operation is executed, and moving the data item to the correct time zone offset value classification node.

[0022] 3.3 Index maintenance: Perform index maintenance operations regularly to optimize the index structure and improve query efficiency. Maintenance strategies include rebalancing the time zone offset value classification nodes, cleaning up invalid data items, and optimizing the index storage structure.

[0023] Next, the present invention will be described in detail with reference to the following specific embodiments: Example 1: Assume that there is a table containing time fields and time zone information in the database, and we need to create an index structure based on the time zone offset value for the table.

[0024] Step 1: Initialize the time zone offset value classification node; Step 2: When inserting data, calculate the time zone offset value of the data's time field and time zone information, and store the data under the corresponding time zone offset value classification node; Step 3: When querying data, locate the corresponding time zone offset value classification node according to the time zone information in the query conditions to quickly obtain the query results.

[0025] Example 2: Time Zone Change Processing Assume that the offset value of a time zone in the database is changed from UTC+2 to UTC+3.

[0026] Step 1: Detect a time zone change event; Step 2: Lock the affected time zone offset value classification node (UTC+2); Step 3: Traverse all data items under the node and recalculate the time zone offset value of each data item; Step 4: Migrate the recalculated data items to the new time zone offset value classification node (UTC+3); Step 5: Release the lock of the original time zone offset value classification node (UTC+2) and decide whether to keep or delete the node as needed.

[0027] Embodiment 3: Data Update Synchronization Assume that the time field of a record in the database is updated.

[0028] Step 1: Perform data update operation; Step 2: Recalculate the time zone offset value of the record based on the new time field and time zone information; Step 3: If the new time zone offset value is different from the original time zone offset value, move the record from the original time zone offset value classification node to the new time zone offset value classification node; Step 4: Update the index structure to reflect the data change.

[0029] Example 4: Index maintenance The following index maintenance steps are performed regularly: Step 1: Analyze index usage and identify query hotspots and non-hotspot data; Step 2: Optimize the cache of hotspot data to improve query efficiency; Step 3: Compress and store non-hotspot data to reduce storage space; Step 4: Detect and process index fragmentation, and optimize the index structure by merging or reorganizing index nodes.

[0030] On the other hand, with respect to the database index structure based on the time zone offset value and its implementation method disclosed in this solution, the following specific embodiments are proposed: Example 5: Dynamic migration process of time zone offset value Step S101: Shadow node creation and initialization When a time zone rule change is detected (such as an IANA time zone database version upgrade), the dynamic time zone mapping module parses the change log to identify the affected time zones and the difference between the old and new offset values. For each time zone offset value classification node that needs to be adjusted, a temporary shadow node is created in the adjacent storage area. The shadow node naming rule is "original offset value_new offset value_timestamp", and its mixed index structure is initialized.

[0031] Step S102: Double writing and verification in batches The original node data is migrated in batches, with 1,000 data records processed in each batch. During the migration process: 1. Read the original node data block, use the improved Delta encoding algorithm to compress the time difference, and embed the time zone metadata dictionary (including time zone ID and offset value change history) in the compressed block header; 2. Write the compressed data to the original node and the shadow node at the same time. After writing, calculate the CRC32 checksum of the two copies. If the checksum is inconsistent, trigger the automatic retry mechanism (up to 3 times); 3. After each batch of migration is completed, record the migration progress and verification results in the transaction log.

[0032] Step S103: Atomic switching and resource recycling When all data batch migration verifications are passed, the atomic pointer switching operation is performed during the database transaction commit phase: 1. Replace the B+ tree root pointer of the original node with the shadow node address; 2. The original node is marked as a historical version and enters read-only mode, and new write requests are automatically routed to the shadow node; 3. Start the asynchronous garbage collection thread and release its storage space after confirming that there are no active queries accessing the original node.

[0033] Example 6: Implementation details of hybrid index structure Index layer composition: 1. B+ tree main index layer: the key value structure is "15-minute granularity time zone offset value + UTC timestamp", for example "+08:00_1698000000"; leaf nodes store physical address pointers, which are distributed in order according to the time range, supporting efficient range queries; 2. Hash mapping relationship table: The hash key is a combination of adjacent time zone offset values ​​(such as "+08:00→+09:00"), and the hash value stores the time zone conversion function and the jump table pointer; the conversion function is implemented in the form of "UTC time + fixed offset", for example, converting UTC time to the target time zone time; By setting up a horizontal sharding mechanism, when the data volume of a single time zone offset value classification node exceeds 100,000, the time sliding window sharding is triggered: 1. Divide the node data into multiple sub-nodes according to the time range (such as 2020-2022, 2023 to present); 2. Each child node maintains an independent Bloom filter and stores the timestamp hash value to quickly exclude non-query intervals; 3. The parent node retains a virtual pointer and records the time range boundaries of the child nodes to form a hierarchical index topology.

[0034] Example 7: Time zone aware query optimizer workflow Query rewrite phase: 1. Parse the time zone information in the original query conditions, for example, "Beijing time 2023-10-01 08:00 to Tokyo time 2023-10-02 17:00"; 2. Convert to the standardized UTC time range: "UTC 2023-09-30T16:00:00 to UTC 2023-10-02T08:00:00"; 3. According to the time zone offset value mapping table, determine the time zone offset value classification node to be accessed (such as +08:00, +09:00 nodes); Distributed merge scan: 1. Generate a parallel execution plan and scan multiple relevant time zone offset value classification nodes at the same time; 2. Use the skip table structure to quickly locate cross-time zone data, and use the bitmap OR operation to accelerate deduplication when merging intermediate results; 3. Enable memory cache for hot data areas (such as the last three months) and directly return cached results.

[0035] Example 8: Response to time zone rule change event Incremental data repartitioning: 1. The time zone change monitoring service captures rule update events in real time and generates an incremental migration task queue; 2. Generate a fingerprint identifier (MD5 hash value) for the affected data and record the mapping relationship between the offset values ​​before and after the change; 3. Start the background migration task during the system off-peak period, and update the progress log after each batch is completed; Rollback mechanism: 1. Record the original node address and data fingerprint in the migration transaction log; 2. If an exception occurs during the migration process (such as verification failure exceeding the threshold), the rollback process is automatically triggered: a) Terminate the query route pointing to the shadow node; b) Write the migrated data back to the original node according to the log; c) Reset the B+ tree pointer to the state before migration.

[0036] Those of ordinary skill in the art will appreciate that the units and method steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in terms of function in the above description. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

[0037] In the several embodiments provided in the present application, it should be understood that the disclosed methods and systems can be implemented in other ways. For example, the division of the units described above is only a logical function division, and there may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. The above-mentioned units may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the embodiments of the present invention.

[0038] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein by equivalents. These modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present invention, and they should all be included in the scope of the claims and specification of the present invention.

[0039] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A database indexing method based on time zone offset value, characterized in that: include: When the time zone information in the database changes, the affected time zone offset value classification node is first identified, and the time zone offset values ​​of all data items in the node are recalculated; Migrate the recalculated data items from the original time zone offset value classification node to the new time zone offset value classification node; After migrating the data item, release the original time zone offset value classification node and decide whether to keep or delete the node as needed; The specific method of migrating the recalculated data items from the original time zone offset value classification node to the new time zone offset value classification node is: using a shadow node cluster and a dual write mechanism: a) Create a temporary shadow node with the new offset value as the key; b) Synchronously update the original node and shadow node; c) Verify data consistency through CRC32; d) Atomically switch node pointers during the transaction commit phase.

2. The database indexing method based on time zone offset value according to claim 1, characterized in that: The time zone information change is the change of the offset value caused by the daylight saving time adjustment or the change of the time zone rules; For large-scale migration, batch pipeline processing is adopted, and each batch contains a preset number of data blocks; During the migration process, the original node remains in a read-only state, and the newly written data is directly directed to the shadow node.

3. The database indexing method based on time zone offset value according to claim 1, characterized in that: Includes updated sync methods: After executing the data update operation, recalculate the time zone offset value of the data, and update the time zone offset value classification node of the data item according to the calculation result; If the new time zone offset value is different from the original time zone offset value, the data item is migrated from the original time zone offset value classification node to the new time zone offset value classification node; Update database index structure; Establish a time zone change event monitoring queue to capture time zone rule change events; adopt an incremental data repartitioning strategy to generate migration data fingerprints and establish a rollback log.

4. The database indexing method based on time zone offset value according to claim 3, characterized in that: The data updating operation includes updating the time field in the database table; Build a time zone-aware query optimizer to convert original query statements into standardized UTC expressions and generate distributed merge index scan plans for range queries involving multiple time zones.

5. The database indexing method based on time zone offset value according to claim 3, characterized in that: include: Analyze the usage of indexes and identify hot and non-hot data; Optimize cache of hot data to reduce query time; Compress and store non-hotspot data to reduce storage space usage; Detect and handle index fragmentation, and optimize the index structure by merging, reorganizing, or rebalancing nodes classified by time zone offset values; Wherein, the merging, reorganizing or rebalancing the time zone offset value classification nodes improves query efficiency by adjusting the load distribution of the nodes; The compression storage adopts an improved Delta coding algorithm to store time differences instead of absolute values; The time zone metadata dictionary is embedded in the compressed data block header, which contains all the time zone conversion rules of the node.

6. A database index structure based on a time zone offset value, characterized in that: It includes multiple time zone offset value classification nodes, each node corresponds to a specific time zone offset value, and the data stored under each time zone offset value classification node is sorted according to the absolute value of the time data; The time zone offset value classification nodes are pre-generated according to the global time zone offset value range, and each node is set and stored in the index part of the database when the database is initialized; The structure also includes a dynamic time zone offset value mapping module, which detects daylight saving time adjustments and time zone rule changes in real time and generates an offset value increment table; Each classification node stores sub-interval data with time zone offset values ​​in the range of [-12h, +14h] and with 15 minutes as the minimum granularity unit. Cross-time zone associations are established between nodes through a skip table structure.

7. The database index structure based on time zone offset value according to claim 6, characterized in that: include: When the database is initialized, a time zone offset value classification node is pre-generated according to the global time zone offset value range and stored in the index part of the database; When inserting data, the time zone offset value is obtained by calculating the time field and time zone information of the data, and the data is stored in the corresponding time zone offset value classification node; When deleting data, locate the corresponding time zone offset value classification node by calculating the time field and time zone information of the data, and delete the data item from the node; When querying data, quickly locate the corresponding time zone offset value classification node based on the time zone information in the query conditions; The time zone offset value classification node adopts a multi-level hybrid index layer, which is composed of a B+ tree primary index and a hash mapping relationship table, wherein the hash table stores adjacent time zone conversion functions f(x)=UTC_time+offset_x; Each node sets a capacity threshold monitor. When the data volume exceeds the threshold, horizontal sharding is triggered to generate a chain of child nodes with the same offset value.

8. The database index structure based on time zone offset value according to claim 7, characterized in that: The query data operation directly accesses the relevant time data item by finding the index address of the time zone offset value classification node; The horizontal sharding uses a time sliding window algorithm to divide the node data into multiple sub-nodes, and each sub-node maintains an independent Bloom filter to quickly exclude non-query time intervals; During the sharding process, the virtual index pointer of the parent node is retained to form a hierarchical index topology.

9. A server, characterized in that: It includes at least one processor and a memory communicatively connected to the processor, wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the processor so that the at least one processor executes a database indexing method based on a time zone offset value as described in any one of claims 1-5.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method for indexing a database based on a time zone offset value as described in any one of claims 1 to 5 is implemented.

Images