A Database Indexing Method Based on Time Zone Offset Values

Abstract

The present invention provides a database indexing method based on time zone offset values, including multiple 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 during database initialization. Advantageous effects of the present invention: The database index structure based on time zone offset values and its implementation method improve the efficiency of time zone data query, reduce the complexity of the database in processing time zone data, are applicable to time zone data processing worldwide, and have good scalability and maintainability.

Description

Technical Field

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

[0002] Most of the existing database indexing technologies index the absolute values of time data. When dealing with time data involving time zones, the query efficiency is relatively low. Especially in the global business scenarios, the processing requirements of time zone data are increasing day by day, and the existing indexing technologies can no longer meet the requirements of efficient query. Summary of the Invention

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

[0004] To achieve the above object, the technical solution of the present invention is realized as follows:

[0005] A database indexing method based on time zone offset values, comprising:

[0006] When the time zone information in the database changes, first identify the affected time zone offset value classification nodes, and recalculate the time zone offset values of all data items within the nodes;

[0007] Migrate the recalculated data items from the original time zone offset value classification nodes to the new time zone offset value classification nodes;

[0008] After migrating the data items, release the original time zone offset value classification nodes, and decide whether to retain or delete the nodes as needed;

[0009] The migration process adopts a shadow node cluster and a double-write mechanism:

[0010] a) Create a temporary shadow node with the new offset value as the key;

[0011] b) Synchronously update the original node and the shadow node;

[0012] c) Verify data consistency through CRC32;

[0013] d) Atomically switch the node pointers during the transaction commit phase.

[0014] Furthermore, the change of the time zone information is the change of the offset value caused by daylight saving time adjustment or time zone rule change;

[0015] For large-scale migration, batch pipelining processing is adopted, and each batch contains a preset number of data blocks;

[0016] During the migration process, the original node remains read-only, and newly written data is directly directed to the shadow node.

[0017] Furthermore, it includes an update synchronization method:

[0018] After performing 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;

[0019] If the new time zone offset value is different from the original time zone offset value, migrate the data item from the original time zone offset value classification node to the new time zone offset value classification node;

[0020] Update the database index structure to ensure data consistency;

[0021] Establish a time zone change event listening queue to capture time zone rule change events; adopt an incremental data repartitioning strategy to generate migration data fingerprints and establish a rollback log.

[0022] Furthermore, the data update operation includes updating the time field in the database table;

[0023] Construct a time zone-aware query optimizer to convert the original query statement into a standardized UTC expression, and generate a distributed merge index scan plan for range queries involving multiple time zones.

[0024] Furthermore, it includes:

[0025] Analyze the usage of indexes to identify hot and non-hot query data;

[0026] Perform cache optimization on hot data to reduce query time;

[0027] Perform compressed storage on non-hot data to reduce storage space occupancy;

[0028] Detect and process index fragmentation, and optimize the index structure by merging, reorganizing, or rebalancing the time zone offset value classification nodes;

[0029] Among them, the merging, reorganizing, or rebalancing of the time zone offset value classification nodes improves query efficiency by adjusting the load distribution of the nodes;

[0030] The compressed storage adopts an improved Delta coding algorithm to store time differences instead of absolute values;

[0031] Embed a time zone metadata dictionary in the compressed data block header, including all time zone conversion rules of the node.

[0032] On the other hand, the present invention provides a database index structure based on time zone offset values, including multiple 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;

[0033] 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 during database initialization;

[0034] The structure further 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;

[0035] Each classification node stores sub-interval data with time zone offset values in the range of [-12h, +14h] and a minimum granularity unit of 15 minutes, and cross-time zone associations are established between nodes through a skip list structure.

[0036] Further, it includes:

[0037] During database initialization, time zone offset value classification nodes are pre-generated according to the global time zone offset value range and stored in the index part of the database;

[0038] When inserting data, the time zone offset value is obtained by calculating the time field of the data and the time zone information, and the data is stored in the corresponding time zone offset value classification node;

[0039] When deleting data, the corresponding time zone offset value classification node is located by calculating the time field of the data and the time zone information, and the data item is deleted from this node;

[0040] When querying data, according to the time zone information in the query condition, quickly locate the corresponding time zone offset value classification node;

[0041] The time zone offset value classification node adopts a multi-level hybrid index layer, which consists of a B+ tree main index and a hash mapping relation table, where the hash table stores the adjacent time zone conversion function f(x) = UTC_time + offset_x;

[0042] Each node is provided with a capacity threshold monitor, which triggers horizontal sharding when the data volume exceeds the threshold, generating a chain of child nodes with the same offset value.

[0043] Further, the data query operation directly accesses the relevant time data items by looking up the index address of the time zone offset value classification node;

[0044] The horizontal sharding uses a time sliding window algorithm to divide the node data into multiple child nodes, and each child node maintains an independent Bloom filter for quickly excluding non-query time intervals;

[0045] During the fragmentation process, the virtual index pointer of the parent node is retained to form a hierarchical index topology.

[0046] On the other hand, the present invention provides a server, including at least one processor, and a memory communicatively connected to the processor. The memory stores instructions executable by the at least one processor. When the instructions are executed by the processor, the at least one processor is caused to execute any one of the database index methods based on time zone offset values.

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

[0048] Compared with the prior art, the database index structure based on time zone offset values and its implementation method of the present invention have the following beneficial effects:

[0049] (1) The database index structure based on time zone offset values and its implementation method and structure of the present invention improve the efficiency of time zone data query, reduce the complexity of the database in processing time zone data, are applicable to the processing of time zone data globally, and have good scalability and maintainability;

[0050] (2) The database index structure based on time zone offset values and its implementation method and structure of the present invention achieve 15-minute granular time zone coverage (supporting 576 time zone intervals) and second-level rule change response through a dynamic time zone mapping module, improve the cross-time zone query efficiency by combining a hybrid index (B+tree + hash table), ensure zero perception of time zone migration through a shadow node double-write mechanism, and cooperate with a time zone awareness optimizer to reduce the time-consuming of complex query parsing. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] The drawings constituting a part of the present invention are used to provide a further understanding of the present invention. The schematic embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation to the present invention. In the drawings:

[0052] Figure 1 It is a schematic diagram of the method of the embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

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

[0054] The present invention will be described in detail below with reference to the drawings and in combination with the embodiments.

[0055] On the one hand, as Figure 1As shown in the figure, the present invention provides a database index structure based on time zone offset values, specifically as follows:

[0056] 1. Index structure design: The index structure includes multiple time zone offset value classification nodes, each node corresponding 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.

[0057] 2. Index construction method: When the database is initialized, time zone offset value classification nodes are pre-generated according to the global time zone offset value range; when inserting data, the time zone offset value is calculated based on the time field and time zone information of the data, and the data is stored under 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 condition, improving the query efficiency.

[0058] 3. Index update strategy:

[0059] 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, and after recalculating the time zone offset values of the data in these nodes, migrating them to the new time zone offset value classification nodes.

[0060] 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 immediately recalculating the time zone offset value according to the new time field and time zone information after the data update operation is executed, and moving the data item to the correct time zone offset value classification node.

[0061] 3.3 Index maintenance: Regularly perform index maintenance operations to optimize the index structure and improve the query efficiency. The maintenance strategy includes rebalancing the time zone offset value classification nodes, cleaning up invalid data items, and optimizing the index storage structure.

[0062] Example 1: Suppose there is a table in the database that contains a time field and time zone information. We need to create an index structure based on time zone offset values for this table.

[0063] Step 1: Initialize the time zone offset value classification nodes; Step 2: When inserting data, calculate the time zone offset value of the time field and time zone information of the data, 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 condition, and quickly obtain the query result.

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

[0065] 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 this 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 on the original time zone offset value classification node (UTC+2) and decide whether to retain or delete this node as needed.

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

[0067] Step 1: Execute a 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.

[0068] Example 4: Index Maintenance Periodically perform the following index maintenance steps:

[0069] Step 1: Analyze the index usage situation, identify query hotspots and non-hotspot data; Step 2: Optimize the cache for hotspot data to improve query efficiency; Step 3: Compress and store non-hotspot data to reduce storage space occupation; Step 4: Detect and process index fragmentation, and optimize the index structure by merging or reorganizing index nodes.

[0070] On the other hand, for the database index structure based on time zone offset values and its implementation method disclosed in this solution, the following specific examples are proposed:

[0071] Example 5: Dynamic Migration Process of Time Zone Offset Value

[0072] Step S101: Creation and Initialization of Shadow Nodes

[0073] When a time zone rule change is detected (such as an upgrade of the IANA time zone database version), the dynamic time zone mapping module parses the change log to identify the affected time zones and the differences between their old and new offset values. For each time zone offset value classification node that needs to be adjusted, create a temporary shadow node in the adjacent storage area. The naming rule for the shadow node is "original offset value_new offset value_timestamp", and initialize its hybrid index structure.

[0074] Step S102: Batch Double Writing and Verification

[0075] Perform batch migration on the original node data, processing 1000 data records per batch. During the migration process:

[0076] 1. Read the original node data block, compress the time difference using an improved Delta encoding algorithm, and embed a time zone metadata dictionary (including time zone ID and offset value change history) in the compressed block header;

[0077] 2. Write the compressed data to both the original node and the shadow node simultaneously. After the write is completed, calculate the CRC32 checksum of the double copies. When the checksums are inconsistent, trigger the automatic retry mechanism (up to 3 times);

[0078] 3. After each batch migration is completed, record the migration progress and verification results in the transaction log.

[0079] Step S103: Atomic Switching and Resource Recycling

[0080] When all data batch migrations are verified successfully, perform an atomic pointer switching operation during the database transaction commit phase:

[0081] 1. Replace the B+ tree root pointer of the original node with the shadow node address;

[0082] 2. Mark the original node as a historical version and enter read-only mode. New write requests are automatically routed to the shadow node;

[0083] 3. Start an asynchronous garbage collection thread. After confirming that there is no active query accessing the original node, release its storage space.

[0084] Example 6: Implementation Details of the Hybrid Index Structure

[0085] Composition of the index layer:

[0086] 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"; The leaf nodes store physical address pointers, and the pointers are distributed in an orderly manner according to the time range, supporting efficient range queries;

[0087] 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 skiplist pointer; The implementation form of the conversion function is "UTC time + fixed offset", for example, converting UTC time to the target time zone time;

[0088] By setting a horizontal sharding mechanism, when the data volume of a single time zone offset value classification node exceeds 100,000, trigger the time sliding window sharding:

[0089] 1. Divide the node data into multiple sub-nodes according to the time range (such as 2020 - 2022, and from 2023 until now);

[0090] 2. Each sub-node maintains an independent Bloom filter, storing timestamp hash values for quickly excluding non-query intervals;

[0091] 3. The parent node retains virtual pointers, recording the time range boundaries of the sub-nodes to form a hierarchical index topology.

[0092] Example 7: Working Process of Time Zone-Aware Query Optimizer

[0093] Query Rewriting Phase:

[0094] 1. Parse the time zone information in the original query condition, for example, convert "Beijing time from 08:00 on October 1, 2023 to Tokyo time at 17:00 on October 2, 2023";

[0095] 2. Convert it to a standardized UTC time range: "UTC from 16:00:00 on September 30, 2023 to 08:00:00 on October 2, 2023";

[0096] 3. According to the time zone offset value mapping table, determine the time zone offset value classification nodes to be accessed (such as +08:00, +09:00 nodes);

[0097] Distributed Merge Scan:

[0098] 1. Generate a parallel execution plan to scan multiple relevant time zone offset value classification nodes simultaneously;

[0099] 2. Use the skip list structure to quickly locate cross-time zone data, and adopt the bitmap OR operation to accelerate deduplication when merging intermediate results;

[0100] 3. Enable memory caching for hot data areas (such as the most recent 3 months) and directly return the cached results.

[0101] Example 8: Response to Time Zone Rule Change Events

[0102] Incremental Data Repartitioning:

[0103] 1. The time zone change monitoring service captures rule update events in real-time and generates an incremental migration task queue;

[0104] 2. Generate fingerprint identifiers (MD5 hash values) for the affected data, recording the offset value mapping relationship before and after the change;

[0105] 3. Start background migration tasks during the system's low peak period and update the progress log for each completed batch;

[0106] Rollback Mechanism:

[0107] 1. Record the original node address and data fingerprint in the migration transaction log;

[0108] 2. If an exception occurs during the migration (such as the verification failure exceeding the threshold), automatically trigger the rollback process: a) Terminate the query routing pointing to the shadow node; b) Reverse-write the migrated data back to the original node according to the log; c) Reset the B+ tree pointer to the state before migration.

[0109] Those of ordinary skill in the art can realize that the units and method steps of each example described in combination with the embodiments disclosed herein can be implemented by electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described according to functions in the above description. Whether these functions are executed in a hardware or software manner depends on the specific application and design constraints of the technical solution. Professional technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered to exceed the scope of the present invention.

[0110] In 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 above division of units is only a logical function division, and there can be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. The above units may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they can be located in one place, or distributed to multiple network units. Part or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiments of the present invention.

[0111] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements for some or all of the technical features; and 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 covered by the scope of the claims and the description of the present invention.

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

Claims

1. A database indexing method based on time zone offset values, characterized in that, including: When the time zone information in the database changes, first identify the affected time zone offset value classification nodes, and recalculate the time zone offset values of all data items within these nodes; Migrate the recalculated data items from the original time zone offset value classification nodes to the new time zone offset value classification nodes; After migrating the data items, release the original time zone offset value classification nodes, and decide whether to retain or delete these nodes as needed; Among them, the specific method of migrating the recalculated data items from the original time zone offset value classification nodes to the new time zone offset value classification nodes is as follows: adopt 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 the shadow node; c) Verify data consistency through CRC32; d) Atomically switch the node pointer during the transaction commit phase; including multiple 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 during database initialization and stored in the index part of the database; Detect daylight saving time adjustments and time zone rule changes in real time, and generate an offset value increment table; Each classification node stores sub-interval data with a time zone offset value in the range of [-12h, +14h] and a minimum granularity of 15 minutes, and cross-time zone associations are established between nodes through a skip list structure.

2. The database indexing method based on time zone offset values according to claim 1, wherein: The time zone information change is the offset value change caused by daylight saving time adjustment or time zone rule change; For large-scale migrations, use batch pipelining processing, with each batch containing a preset number of data blocks; During the migration process, the original node remains read-only, and newly written data is directly directed to the shadow node.

3. A database indexing method based on a time zone offset value according to claim 1, characterized in that, including an update synchronization method: After performing a data update operation, recalculate the time zone offset value of the data, and update the time zone offset value classification node of this data item according to the calculation result; If the new time zone offset value is different from the original time zone offset value, then migrate the data item from the original time zone offset value classification node to the new time zone offset value classification node; Update the database index structure; Establish a time zone change event listening queue to capture time zone rule change events; adopt an incremental data re-partitioning strategy to generate migration data fingerprints and establish a rollback log.

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

5. A database indexing method based on a time zone offset value according to claim 3, characterized in that including: Analyze the usage of indexes, and identify hot query data and non-hot query data; Perform cache optimization on hot query data to reduce query time; Perform compressed storage on non-hot query data to reduce storage space occupancy; Detect and process index fragmentation, and optimize the index structure by merging, reorganizing, or rebalancing the time zone offset value classification nodes; Among them, the merging, reorganizing, or rebalancing of the time zone offset value classification nodes improves the 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; Embed a time zone metadata dictionary at the head of the compressed data block, which contains all time zone conversion rules of the node.

6. The database indexing method based on time zone offset values according to claim 1, wherein It includes: During database initialization, generate time zone offset value classification nodes in advance according to the global time zone offset value range, and store them in the index part of the database; When inserting data, obtain the time zone offset value by calculating the time field of the data and the time zone information, and store the data 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 of the data and the time zone information, and delete the data item from this node; When querying data, quickly locate the corresponding time zone offset value classification node according to the time zone information in the query condition; The time zone offset value classification node adopts a multi-level hybrid index layer, which consists of a B+ tree main index and a hash mapping relationship table, where the hash table stores the adjacent time zone conversion function f(x)=UTC_time + offset_x; Each node is provided with a capacity threshold monitor, which triggers horizontal sharding when the data volume exceeds the threshold, and generates a chain of child nodes with the same offset value.

7. A database indexing method based on a time zone offset value according to claim 6, characterized in that: The data query operation directly accesses the relevant time data items by looking up 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 child nodes, and each child node maintains an independent Bloom filter for quickly excluding non-query time intervals; Retain the virtual index pointer of the parent node during the sharding process to form a hierarchical index topology.

8. A server, characterized in that: It includes at least one processor and a memory communicatively connected to the processor. The memory stores instructions executable by the at least one processor. When the instructions are executed by the processor, the at least one processor is enabled to execute a database indexing method based on time zone offset values according to any one of claims 1-7.

9. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, it implements a database indexing method based on time zone offset values according to any one of claims 1-7.

Images