Large-scale business data synchronization and query method and system based on double-table alternation and pre-paging index, electronic equipment and storage medium

Abstract

The invention provides a large-scale business data synchronization and query method and system based on double-table alternation and pre-paging index, electronic equipment and a storage medium, and belongs to the technical field of databases. The method comprises the following steps of: in a data preprocessing stage, generating a serial number which is continuously increased by utilizing a window function, persisting, and constructing a pre-paging index; constructing a double-buffer table and a state management table in the target database; periodically executing dual-stage synchronization including historical correction and current increment on the inactive table, and performing atomic switching through the state table after the synchronization is completed; during query, the time slice statistical information and the pre-paging index in the metadata are combined, and paging query is converted into accurate primary key range scanning. According to the method, the problem of performance degradation of deep paging query is thoroughly solved by pre-distributing the serial numbers, zero-shutdown data synchronization is realized by utilizing a double-table mechanism, the consistency and integrity of the data are guaranteed through a double-stage synchronization strategy, and the method is particularly suitable for an online query scene of large-scale business data such as invoices.

Description

Technical Field

[0001] This invention relates to the fields of database technology and big data processing, and particularly to a method for synchronizing and querying large-scale business data (such as invoice data) based on alternating dual tables and pre-paging indexes in a scenario of synchronizing from a distributed data warehouse to a relational database. Specifically, it relates to a method, system, electronic device, and storage medium for synchronizing and querying large-scale business data based on alternating dual tables and pre-paging indexes. Background Technology

[0002] In the process of enterprise digital transformation, massive amounts of business data (such as hundreds of thousands of invoices for a single enterprise) are usually stored in distributed data warehouses (such as Hive) for offline analysis, and also need to be periodically synchronized to relational databases (such as TiDB, MySQL, etc.) to support online queries by front-end business applications.

[0003] However, existing technologies face significant challenges in handling the synchronization and pagination queries of such large-scale data: 1. Performance degradation of pagination queries: Traditional pagination mainly relies on OFFSET and LIMIT syntax. When the data volume is huge, querying deep pagination (such as page 100) requires the database to scan and discard all previous data, with a time complexity of O(n), causing the response time to degrade from milliseconds to seconds or even timeout.

[0004] 2. Data synchronization causes service interruption: Traditional full synchronization usually requires locking or clearing tables, resulting in service unavailability; ordinary incremental synchronization may cause inconsistent query results during execution.

[0005] 3. Low query efficiency in the time dimension: Without targeted optimization, queries by time range such as month often trigger full table scans or inefficient index scans.

[0006] 4. Difficulty in correcting historical data: Simple incremental synchronization is difficult to capture historical data that has changed in the previous period (such as delayed data such as invoice cancellation or voiding), resulting in inconsistency between the source and target data.

[0007] Therefore, there is an urgent need for a technical solution that can simultaneously address the performance bottleneck of deep pagination, achieve zero-downtime synchronization, and ensure data consistency. Summary of the Invention

[0008] The purpose of this invention is to provide a method for synchronizing and querying large-scale business data based on alternating dual tables and pre-paginated indexes. By building a sequence index in the data preprocessing stage and maintaining dual buffer tables and metadata in the synchronization stage, the method fundamentally improves query performance and ensures service continuity.

[0009] The first aspect of this invention discloses a method for synchronizing and querying large-scale business data based on alternating dual tables and pre-pagination indexes; the method includes: Step S1: Data Preprocessing and Index Building At the data source end, the raw business data is grouped according to the subject identifier and sorted according to the time dimension within the group; a continuously increasing sequence number is generated using window functions, and the sequence number is attached to the business data as a persistent field to form pre-processed data with a pre-pagination index; Step S2: Double buffer table initialization and synchronization environment construction In the target database, construct a first and second data table with isomorphic structure, as well as a status management table to record the current active table identifiers; in the initial state, mark one of the data tables as an active table to provide query services to the outside world, and mark the other as an inactive table for data synchronization; Step S3: Metadata Construction During data synchronization, using time slicing as the granularity, the lower bound of the sequence number, the upper bound of the sequence number, and the total amount of data for each subject identifier in each time slice are statistically analyzed, and metadata with a hierarchical nested structure is constructed and stored. Step S4: Two-stage incremental synchronization and switching Periodically perform two-stage incremental synchronization on inactive tables, including the first stage of historical correction synchronization and the second stage of current incremental synchronization; after synchronization is completed and verified to be error-free, insert a new record into the status management table to switch the active table identifier; Step S5: High-performance query based on metadata Upon receiving a query request, the system reads the status management table to determine the currently active table; based on the subject identifier and time range in the query request, it retrieves metadata to locate the target sequence number range; and performs a range query in the active table based on the sequence number range.

[0010] Preferably, in step S1, generating a continuously increasing sequence number specifically includes: Partition window aggregation operator using a distributed computing framework; Set the main identifier as the partition key, and set the business occurrence time and unique identifier as the sort key; The generated serial number is a globally unique and consecutive integer within the same subject identifier range; The serial number and the subject identifier together constitute a composite primary key index in the target database.

[0011] Preferably, in step S3, the metadata of the hierarchical nested structure is stored in JSON format, and its structure includes: Top-level fields: contain the main identifier and global statistical indicators; Nested layer: Contains a time-sliced ​​statistics array sorted in ascending order of time; Each element in the time slice statistics array contains: a time identifier, the total number of records in that time slice, the minimum sequence number in that time slice, and the maximum sequence number in that time slice.

[0012] Preferably, in step S4, the two-stage incremental synchronization specifically includes: Phase 1: Acquire the data that has changed in the previous synchronization cycle, recalculate its sequence number and write it into the inactive table to process the delayed historical correction data; Phase 2: Acquire new data generated within the current synchronization cycle, calculate its sequence number, and write it to the inactive table; When writing to inactive tables, an idempotent update mechanism based on the primary key is used to prevent data duplication.

[0013] Preferably, the status management table adopts an append-only write mode; The switching of the active table identifier specifically means: after synchronization is completed and data integrity verification is passed, inserting a new record containing the latest active table name and the current timestamp into the status management table; During a query, the currently active table is determined by sorting the records in descending order by timestamp and retrieving the first record.

[0014] Preferably, in step S5, retrieving metadata to locate the target sequence number range specifically includes: Retrieve the corresponding metadata JSON object based on the subject identifier in the query request; In the nested time-slice statistics array of metadata, a binary search algorithm is used to locate one or more target time slices corresponding to the query time range; Extract the minimum and maximum sequence numbers of the target time slice, and combine them with the pagination parameters in the query request to calculate the precise start and end values ​​of the sequence numbers required for this pagination query.

[0015] Preferably, in step S5, performing a range query in the active table based on the sequence number range specifically includes: Using the calculated precise sequence number start and end values, construct a range scan query statement targeting the composite primary key of the active table; The database execution engine directly locates the physical storage location of the data by scanning the query statement based on the range, avoiding full table scans or offset-based traversal.

[0016] Preferably, the business data is invoice data, the data source is a distributed data warehouse, and the target database is a relational database; The two-stage incremental synchronization ensures zero interruption of query services and strong data consistency during data synchronization by overlapping time windows.

[0017] A second aspect of this invention discloses a large-scale business data synchronization and query system based on dual-table alternation and pre-pagination indexing; the system includes: The data preprocessing and index building module is configured to group the raw business data according to the subject identifier at the data source end, and sort the data within the group according to the time dimension; generate continuously increasing sequence numbers using window functions, and attach the sequence numbers as persistent fields to the business data to form preprocessed data with pre-paginated indexes; The double-buffered table initialization and synchronization environment construction module is configured to build a first and second data table with isomorphic structure in the target database, as well as a state management table for recording the current active table identifiers; in the initial state, one of the data tables is marked as an active table to provide query services to the outside world, and the other is marked as an inactive table for data synchronization; The metadata building module is configured to, during the data synchronization process, use time shards as the granularity to calculate the lower bound of the sequence number, the upper bound of the sequence number, and the total amount of data for each subject identifier in each time shard, build and store the metadata with a hierarchical nested structure. The two-stage incremental synchronization and switching module is configured to periodically perform two-stage incremental synchronization on inactive tables, including the first stage of historical correction synchronization and the second stage of current incremental synchronization; after synchronization is completed and verified to be error-free, a new record is inserted into the status management table to switch the active table identifier. The high-performance query module based on metadata is configured to receive query requests, read the status management table to determine the currently active table, retrieve metadata to locate the target sequence number range based on the subject identifier and time range in the query request, and perform a range query in the active table based on the sequence number range.

[0018] A third aspect of this invention discloses an electronic device. The electronic device includes a memory and a processor. The memory stores a computer program, and when the processor executes the computer program, it implements the steps of a method for large-scale business data synchronization and querying based on alternating dual tables and pre-paged indexes, as described in any of the first aspects of this disclosure.

[0019] A fourth aspect of this invention discloses a computer-readable storage medium. The computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of a method for large-scale business data synchronization and querying based on alternating dual tables and pre-paged indexes, as described in any of the first aspects of this disclosure.

[0020] The present invention has the following beneficial effects: 1. Completely solve the performance problem of deep pagination: By pre-allocating sequence numbers, pagination queries are transformed into range queries based on primary keys. Regardless of the page number, the query time is consistently in the millisecond range (e.g., 15-30ms).

[0021] 2. Achieve zero-downtime synchronization: The dual-table alternation mechanism achieves physical isolation between read and write operations, and state switching is an atomic operation. Users are unaware of the synchronization process, thus eliminating the service interruption window.

[0022] 3. Supports historical correction and consistency: The two-stage synchronization strategy effectively covers the correction of delayed data, ensuring a high degree of consistency between the data warehouse and the business database.

[0023] 4. Fast time location: Metadata-driven binary search replaces the traditional time field index scan, greatly improving the efficiency of paginated queries with time conditions. Attached Figure Description

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

[0025] Figure 1 A flowchart illustrating a method for synchronizing and querying large-scale business data based on alternating dual tables and pre-pagination indexes according to an embodiment of the present invention; Figure 2 This is a schematic diagram of the overall process and system architecture provided for embodiments of the present invention; Figure 3 This is a schematic diagram illustrating the pre-pagination index structure in an embodiment of the present invention and its comparison with the traditional method; Figure 4 This is a timing diagram of the alternating switching of dual tables and the dual-stage synchronization in an embodiment of the present invention; Figure 5 This is a structural diagram of a large-scale business data synchronization and query system based on dual-table alternation and pre-paged index according to an embodiment of the present invention; Figure 6 This is a structural diagram of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0026] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0027] This invention addresses the synchronization scenario from a distributed data warehouse to a relational database, resolving the issues of severe performance degradation in pagination queries and service interruptions during data synchronization in traditional solutions for large-scale invoice data scenarios.

[0028] Specifically, the present invention achieves the above objectives through the following technical means: (1) By constructing a pre-allocated sequence index during the data preprocessing stage, the performance degradation problem of large offset pagination queries is fundamentally solved from the data level; (2) By designing a monthly statistical metadata structure, a fast range location for time-dimension queries can be achieved; (3) Zero interruption of query service during data synchronization is achieved through the alternating synchronization mechanism of double buffer tables; (4) By using a two-stage incremental synchronization strategy, we can effectively handle historical data correction scenarios and ensure data consistency.

[0029] This invention organically integrates three technologies—pre-allocated sequence index, metadata-driven range positioning, and double-buffered table switching—by constructing an optimized data structure during the data preprocessing and synchronization stages, providing high-performance query support and zero-interruption synchronization capabilities from the data level.

[0030] Example 1: The first aspect of this invention discloses a method for synchronizing and querying large-scale business data based on alternating dual tables and pre-pagination indexes. Figure 1 This is a flowchart illustrating a large-scale business data synchronization and query method based on dual-table alternation and pre-pagination index according to an embodiment of the present invention, as shown below. Figure 1 As shown, the method includes: Step S1: Data Preprocessing and Index Building At the data source end, the raw business data is grouped according to the subject identifier and sorted according to the time dimension within the group; a continuously increasing sequence number is generated using window functions, and the sequence number is attached to the business data as a persistent field to form pre-processed data with a pre-pagination index; In some specific embodiments, the business data is invoice data, the data source is a distributed data warehouse, and the target database is a relational database; The two-stage incremental synchronization ensures zero interruption of query services and strong data consistency during data synchronization by overlapping time windows.

[0031] In step S1, generating a continuously increasing sequence number specifically includes: Partition window aggregation operator using a distributed computing framework; Set the main identifier as the partition key, and set the business occurrence time and unique identifier as the sort key; The generated serial number is a globally unique and consecutive integer within the same subject identifier range; The serial number and the subject identifier together constitute a composite primary key index in the target database.

[0032] Step S2: Double buffer table initialization and synchronization environment construction In the target database, construct a first and second data table with isomorphic structure, as well as a status management table to record the current active table identifiers; in the initial state, mark one of the data tables as an active table to provide query services to the outside world, and mark the other as an inactive table for data synchronization; In some specific embodiments, the state management table adopts an append-only write mode; The switching of the active table identifier specifically means: after synchronization is completed and data integrity verification is passed, inserting a new record containing the latest active table name and the current timestamp into the status management table; During a query, the currently active table is determined by sorting the records in descending order by timestamp and retrieving the first record.

[0033] Step S3: Metadata Construction During data synchronization, using time slicing as the granularity, the lower bound of the sequence number, the upper bound of the sequence number, and the total amount of data for each subject identifier in each time slice are statistically analyzed, and metadata with a hierarchical nested structure is constructed and stored. In step S3, the metadata of the hierarchical nested structure is stored in JSON format, and its structure includes: Top-level fields: contain the main identifier and global statistical indicators; Nested layer: Contains a time-sliced ​​statistics array sorted in ascending order of time; Each element in the time slice statistics array contains: a time identifier, the total number of records in that time slice, the minimum sequence number in that time slice, and the maximum sequence number in that time slice.

[0034] Step S4: Two-stage incremental synchronization and switching Periodically perform two-stage incremental synchronization on inactive tables, including the first stage of historical correction synchronization and the second stage of current incremental synchronization; after synchronization is completed and verified to be error-free, insert a new record into the status management table to switch the active table identifier; In step S4, the two-stage incremental synchronization specifically includes: Phase 1: Acquire the data that has changed in the previous synchronization cycle, recalculate its sequence number and write it into the inactive table to process the delayed historical correction data; Phase 2: Acquire new data generated within the current synchronization cycle, calculate its sequence number, and write it to the inactive table; When writing to inactive tables, an idempotent update mechanism based on the primary key is used to prevent data duplication.

[0035] Step S5: High-performance query based on metadata Upon receiving a query request, the system reads the status management table to determine the currently active table; based on the subject identifier and time range in the query request, it retrieves metadata to locate the target sequence number range; and performs a range query in the active table based on the sequence number range.

[0036] In step S5, retrieving metadata to locate the target sequence number range specifically includes: Retrieve the corresponding metadata JSON object based on the subject identifier in the query request; In the nested time-slice statistics array of metadata, a binary search algorithm is used to locate one or more target time slices corresponding to the query time range; Extract the minimum and maximum sequence numbers of the target time slice, and combine them with the pagination parameters in the query request to calculate the precise start and end values ​​of the sequence numbers required for this pagination query.

[0037] In step S5, the range query performed in the active table based on the sequence number range specifically includes: Using the calculated precise sequence number start and end values, construct a range scan query statement targeting the composite primary key of the active table; The database execution engine directly locates the physical storage location of the data by scanning the query statement based on the range, avoiding full table scans or offset-based traversal.

[0038] The technical solution provided by this invention has the following advantages: 1. Fundamentally solve pagination query performance issues from a data perspective. By pre-generating and persisting sequence numbers during the data preprocessing stage, and combining this with time-sharded metadata for rapid location, the query performance for any page number remains stable. Real-world testing shows that with 500,000 data entries, the performance for querying subsequent pages is optimized from 8-12 seconds in the traditional approach to 15-30 milliseconds, a performance improvement of approximately 400 times. The query time complexity is optimized from O(n) to O(1), completely resolving the performance bottleneck of large offset queries and providing stable query performance guarantees for upstream applications.

[0039] 2. Accelerated Time Range Positioning By building metadata during the synchronization phase, binary search capabilities are provided to downstream applications to replace full table scans, resulting in a 50-100x improvement in time-dimensional filtering performance. Time range queries are optimized from full table scans to precise range positioning, significantly reducing query latency.

[0040] 3. Synchronization Zero-Interruption Guarantee By employing dual-replica isolation and atomic failover mechanisms, the data synchronization process is completely transparent to downstream query services, ensuring business continuity. Compared to traditional single-table update methods that require 30-60 minutes of service interruption, this invention achieves true zero-downtime synchronization.

[0041] 4. Enhanced data consistency A two-stage synchronization strategy ensures the integrity of historical correction data, while idempotent updates based on primary keys prevent data duplication. The append-only write mode of the status table retains a complete switch history, facilitating auditing and problem tracing. In the event of synchronization failure, a quick rollback to the previous stable version is possible, demonstrating the system's strong fault tolerance.

[0042] 5. Version control capabilities The dual-replica mechanism inherently supports version rollback, reducing fault recovery time from hours to seconds. By leveraging the version history in the status table, the synchronization batch where the problem occurred can be quickly located, supporting precise rollback.

[0043] Example 2: In some specific embodiments based on the above method: I. Data Structures and Preprocessing This embodiment uses invoice data as an example. To address the poor performance of offset pagination in existing technologies, this invention proposes performing "pre-indexing" during the ETL (Extract-Transform-Load) stage.

[0044] like Figure 3 As shown, in a distributed data warehouse, data cleaning is performed using computing engines such as Spark or Hive SQL. A key step is generating sequence numbers. Figure 3The diagram illustrates the index structure of the data table, highlighting the sequence number field and its characteristics (continuously increasing, globally unique). It also showcases the design of the composite primary key and compares the sequence number-based query method with the traditional offset method, visually explaining the principles of performance optimization.

[0045] Assume the data table contains the following fields: Enterprise ID, Invoice Code, and Issue Date.

[0046] Execute the following logic: SEQUENCE_NO = ROW_NUMBER() OVER (PARTITION BY EnterpriseID ORDER BYIssueDate ASC, InvoiceCode ASC) The generated SEQUENCE_NO has the following characteristics: 1. Continuous within a group: Invoices from the same company have serial numbers 1, 2, 3...N, with no gaps in between.

[0047] 2. Ordered: The larger the serial number, the later the invoice date.

[0048] Store the SEQUENCE_NO as an entity column in a relational database (such as TiDB) and create a composite primary key: PRIMARY KEY (EnterpriseID, SEQUENCE_NO).

[0049] II. Metadata Structure Design To accelerate monthly queries, this invention incorporates a "metadata table." This table stores invoice statistics for each company, using a hierarchical nested JSON structure.

[0050] The following is a sample of JSON data: { "entity_id": "91110108MA00000000", "total_count": 1200, "time_slices": [ { "month": "2023-01", "count": 100, "min_seq": 1, "max_seq": 100 }, { "month": "2023-02", "count": 50, "min_seq": 101, "max_seq": 150 }, ... ] }

[0051] This structure allows applications to quickly locate the sequence number range corresponding to a certain month (e.g., 2023-02 corresponds to 101-150) through binary search, without scanning the actual data table.

[0052] III. Dual-table alternating synchronization mechanism like Figure 2 and Figure 4 As shown, to solve the synchronization table locking problem, two tables with identical structures are created in the target database. Create Invoice_Table_A and Invoice_Table_B. Also create a Status_Table.

[0053] An example of the Status_Table structure is shown in Table 1: Table 1

[0054] The synchronization process is as follows: 1. Status identification: The synchronization program reads the Status_Table and finds that the currently active table is A, then determines that the inactive table B is the target of this synchronization.

[0055] 2. Two-stage incremental synchronization: Phase 1 (Historical Correction): Retrieve data from the previous period (e.g., the past 7 days) from the data warehouse, regenerate the sequence numbers, and upsert (insert or update) them into table B. This can cover historical data changes such as "invoice red-inking".

[0056] Phase 2 (Current Increment): Extract new data for the current period (e.g., today) from the data warehouse and write it to Table B.

[0057] 3. Metadata update: Synchronously update the metadata records corresponding to table B.

[0058] 4. Atomic Switching: After data validation passes, insert a new record into the Status_Table: {active_table_name: 'Invoice_Table_B', ...}.

[0059] IV. Query Execution Process When a user needs to query the second page of data (10 records per page) for a company in the month "2023-02", the system performs the following steps: 1. Route Query: The application reads the latest record from the Status_Table to determine that the currently active table is Invoice_Table_B. 2. Scope positioning: The application queries metadata and parses JSON.

[0060] Locate the "2023-02" fragment.

[0061] Get the serial number range for this month: [101, 150].

[0062] 3. Calculate the pagination range: Page 2, with 10 items per page, means the offset is 10.

[0063] Starting serial number = Segment start (101) + (page number - 1) * number of items per page (10) = 111.

[0064] End sequence number = 111 + 9 = 120.

[0065] Boundary check: 120 <= 150, valid.

[0066] 4. Index Scan: The application sends SQL to the database: SELECT * FROM Invoice_Table_B WHERE EnterpriseID = '...' ANDSEQUENCE_NO BETWEEN 111 AND 120.

[0067] Because a composite primary key is used, the database engine can directly locate the corresponding B+ leaf node to read the data without scanning the first 110 records, achieving a query complexity of O(1).

[0068] As can be seen from the above embodiments, the present invention perfectly solves the query performance and synchronization stability problems under large-scale data by using the strategies of "space for time" (storing sequence numbers and metadata) and "double buffering" (dual table switching).

[0069] Figure 4 The entire process of switching between the two tables is presented in a timeline format, including three key moments: initial state (table A is active), synchronization in progress (table B synchronizes data), and switchover complete (table B is active). The diagram illustrates the role of the status management table in the switchover process and the time window design for the two-stage incremental synchronization. The bottom box highlights the core advantages: zero-interruption query service, data synchronization performed on inactive tables, and atomic switchover within seconds.

[0070] In summary, the solution proposed in this invention can: 1. Pre-allocated sequence indexing mechanism based on partitioned window aggregation In the data preprocessing stage, window functions of the distributed computing framework are used to pre-generate globally unique, continuously increasing sequence numbers for each subject's data, which are then persistently stored as fields. Through the composite primary key design, pagination queries at any position can directly locate the target data, optimizing the query time complexity from O(n) in the traditional approach to O(1), and ensuring that query performance does not degrade with increasing page numbers. This represents a fundamental optimization of pagination queries at the data structure level.

[0071] 2. Hierarchical nested metadata-driven scope positioning technology A nested data structure containing time-slice statistics was designed to record the sequence number range for each subject and each time slice. During application queries, a binary search algorithm is used to locate the target time slice in O(log n) time complexity. Then, combined with pagination parameters, the precise sequence number range is calculated, achieving a shift from a "full table scan + time filtering" query mode to a "precise range scan". Metadata is decoupled from business data, supporting independent updates and optimizations.

[0072] 3. Double-buffered table alternating synchronization mechanism Drawing inspiration from the blue-green deployment approach in applications, a dual-replica table group is designed at the database level. At any given time, one table provides query services (the active table), while the other is used for data synchronization (the inactive table). After data synchronization is complete, a status table enables atomic switching within seconds, ensuring zero interruption to the query service throughout the process. This mechanism achieves physical isolation between data synchronization and query services, fundamentally solving the service interruption problem of traditional single-table update models.

[0073] 4. Two-stage incremental synchronization strategy Each synchronization task is executed in two phases: the first phase synchronizes the increment of the previous period, handling the correction of delayed historical data; the second phase synchronizes the increment of the current period, handling the latest data changes. This strategy, through overlapping time windows, ensures that all data changes are captured, effectively solving the problem that traditional incremental synchronization cannot handle historical corrections, and guaranteeing data integrity and consistency.

[0074] 5. Supplementary state management mechanism The state management table uses an append-only write mode to record each state transition, rather than updating and overwriting. A new record is inserted for each state transition, preserving complete version history information for easier auditing and problem tracing. This also avoids lock contention that can occur with update operations, supporting high-concurrency state queries. This idempotent update mechanism improves the system's reliability and maintainability.

[0075] 6. Systematic synergistic optimization of the three technologies Pre-allocated sequence indexes provide the data foundation (contiguous sequence number space) for metadata-driven range positioning, metadata provides fast positioning capabilities for high-performance queries (O(log n) time-sliced ​​lookup), and alternating synchronization between two tables provides isolation space for data updates (zero-interruption service). These three technologies are interdependent and work together to form a complete technology loop. This systematic combination is not a simple accumulation of technologies, but a deeply optimized design for specific application scenarios.

[0076] The second aspect of this invention discloses a large-scale business data synchronization and query system based on dual-table alternation and pre-pagination indexing. Figure 5 This is a structural diagram of a large-scale business data synchronization and query system based on dual-table alternation and pre-pagination index according to an embodiment of the present invention; as follows: Figure 5 As shown, the system 100 includes: The data preprocessing and index building module 101 is configured to, at the data source end, group the original business data according to the subject identifier and sort it according to the time dimension within the group; use window functions to generate continuously increasing sequence numbers, and attach the sequence numbers as persistent fields to the business data to form preprocessed data with pre-pagination index; The double-buffered table initialization and synchronization environment construction module 102 is configured to construct a first data table and a second data table with isomorphic structure in the target database, as well as a state management table for recording the current active table identifiers; in the initial state, one of the data tables is marked as an active table to provide query services to the outside world, and the other is marked as an inactive table for data synchronization; Metadata construction module 103 is configured to, during data synchronization, use time sharding as the granularity to calculate the lower bound of the sequence number, the upper bound of the sequence number, and the total amount of data for each subject identifier in each time shard, construct and store the metadata with a hierarchical nested structure. The two-stage incremental synchronization and switching module 104 is configured to periodically perform two-stage incremental synchronization on inactive tables, including the first stage of historical correction synchronization and the second stage of current incremental synchronization; after synchronization is completed and verified to be error-free, a new record is inserted into the status management table to switch the active table identifier. The high-performance query module 105 based on metadata is configured to receive query requests, read the status management table to determine the currently active table, retrieve metadata to locate the target sequence number range based on the subject identifier and time range in the query request, and perform a range query in the active table based on the sequence number range. A third aspect of the present invention discloses an electronic device. The electronic device includes a memory and a processor. The memory stores a computer program, and when the processor executes the computer program, it implements the steps of the large-scale business data synchronization and query method based on dual-table alternation and pre-paging indexes according to any one of the first aspects of the present invention.

[0077] Figure 6 This is a structural diagram of an electronic device according to an embodiment of the present invention, such as... Figure 6 As shown, the electronic device includes a processor, memory, communication interface, display screen, and input device connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, carrier networks, Near Field Communication (NFC), or other technologies. The display screen can be an LCD screen or an e-ink screen. The input device can be a touch layer covering the display screen, buttons, a trackball, or a touchpad mounted on the device's casing, or an external keyboard, touchpad, or mouse.

[0078] Those skilled in the art will understand that Figure 6 The structure shown is merely a structural diagram of the part related to the technical solution of this disclosure and does not constitute a limitation on the electronic device to which the solution of this application is applied. The specific electronic device may include more or fewer components than shown in the figure, or combine certain components, or have different component arrangements.

[0079] A fourth aspect of this invention discloses a computer-readable storage medium. The computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of a large-scale business data synchronization and query method based on alternating dual tables and pre-paged indexes, as described in any of the first aspects of this invention.

[0080] Please note that the technical features of the above embodiments can be combined arbitrarily. For the sake of brevity, not all possible combinations of the technical features in the above embodiments have been described. However, as long as the combination of these technical features does not contradict each other, it should be considered within the scope of this specification. The above embodiments only illustrate several implementation methods of this application, and their descriptions are relatively specific and detailed, but they should not be construed as limiting the scope of the invention patent. It should be pointed out that for those skilled in the art, several modifications and improvements can be made without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this patent application should be determined by the appended claims.

Claims

1. A method for synchronizing and querying large-scale business data based on alternating dual tables and pre-paginated indexes, characterized in that, The method includes: Step S1: Data Preprocessing and Index Building At the data source end, the raw business data is grouped according to the subject identifier and sorted according to the time dimension within the group; a continuously increasing sequence number is generated using window functions, and the sequence number is attached to the business data as a persistent field to form pre-processed data with a pre-pagination index; Step S2: Double buffer table initialization and synchronization environment construction In the target database, construct a first and second data table with isomorphic structure, as well as a status management table to record the current active table identifiers; in the initial state, mark one of the data tables as an active table to provide query services to the outside world, and mark the other as an inactive table for data synchronization; Step S3: Metadata Construction During data synchronization, using time slicing as the granularity, the lower bound of the sequence number, the upper bound of the sequence number, and the total amount of data for each subject identifier in each time slice are statistically analyzed, and metadata with a hierarchical nested structure is constructed and stored. Step S4: Two-stage incremental synchronization and switching Periodically perform two-stage incremental synchronization on inactive tables, including the first stage of historical correction synchronization and the second stage of current incremental synchronization; after synchronization is completed and verified to be error-free, insert a new record into the status management table to switch the active table identifier; Step S5: High-performance query based on metadata Upon receiving a query request, the system reads the status management table to determine the currently active table; based on the subject identifier and time range in the query request, it retrieves metadata to locate the target sequence number range; and performs a range query in the active table based on the sequence number range.

2. The method according to claim 1, characterized in that, In step S1, generating a continuously increasing sequence number specifically includes: Partition window aggregation operator using a distributed computing framework; Set the main identifier as the partition key, and set the business occurrence time and unique identifier as the sort key; The generated serial number is a globally unique and consecutive integer within the same subject identifier range; The serial number and the subject identifier together constitute a composite primary key index in the target database.

3. The method according to claim 1, characterized in that, In step S3, the metadata of the hierarchical nested structure is stored in JSON format, and its structure includes: Top-level fields: contain the main identifier and global statistical indicators; Nested layer: Contains a time-sliced ​​statistics array sorted in ascending order of time; Each element in the time slice statistics array contains: a time identifier, the total number of records in that time slice, the minimum sequence number in that time slice, and the maximum sequence number in that time slice.

4. The method according to claim 1, characterized in that, In step S4, the two-stage incremental synchronization specifically includes: Phase 1: Acquire the data that has changed in the previous synchronization cycle, recalculate its sequence number and write it into the inactive table to process the delayed historical correction data; Phase 2: Acquire new data generated within the current synchronization cycle, calculate its sequence number, and write it to the inactive table; When writing to inactive tables, an idempotent update mechanism based on the primary key is used to prevent data duplication.

5. The method according to claim 1, characterized in that, The status management table adopts an append-only write mode; The switching of the active table identifier specifically means: after synchronization is completed and data integrity verification is passed, inserting a new record containing the latest active table name and the current timestamp into the status management table; During a query, the currently active table is determined by sorting the records in descending order by timestamp and retrieving the first record.

6. The method according to claim 1, characterized in that, In step S5, retrieving metadata to locate the target sequence number range specifically includes: Retrieve the corresponding metadata JSON object based on the subject identifier in the query request; In the nested time-slice statistics array of metadata, a binary search algorithm is used to locate one or more target time slices corresponding to the query time range; Extract the minimum and maximum sequence numbers of the target time slice, and combine them with the pagination parameters in the query request to calculate the precise start and end values ​​of the sequence numbers required for this pagination query.

7. The method according to claim 6, characterized in that, In step S5, the range query performed in the active table based on the sequence number range specifically includes: Using the calculated precise sequence number start and end values, construct a range scan query statement targeting the composite primary key of the active table; The database execution engine directly locates the physical storage location of the data by scanning the query statement based on the range, avoiding full table scans or offset-based traversal.

8. A large-scale business data synchronization and query system based on dual-table alternation and pre-pagination index, characterized in that, The system includes: The data preprocessing and index building module is configured to group the raw business data according to the subject identifier at the data source end, and sort the data within the group according to the time dimension; generate continuously increasing sequence numbers using window functions, and attach the sequence numbers as persistent fields to the business data to form preprocessed data with pre-paginated indexes; The double-buffered table initialization and synchronization environment construction module is configured to build a first and second data table with isomorphic structure in the target database, as well as a state management table for recording the current active table identifiers; in the initial state, one of the data tables is marked as an active table to provide query services to the outside world, and the other is marked as an inactive table for data synchronization; The metadata building module is configured to, during the data synchronization process, use time shards as the granularity to calculate the lower bound of the sequence number, the upper bound of the sequence number, and the total amount of data for each subject identifier in each time shard, build and store the metadata with a hierarchical nested structure. The two-stage incremental synchronization and switching module is configured to periodically perform two-stage incremental synchronization on inactive tables, including the first stage of historical correction synchronization and the second stage of current incremental synchronization; after synchronization is completed and verified to be error-free, a new record is inserted into the status management table to switch the active table identifier. The high-performance query module based on metadata is configured to receive query requests, read the status management table to determine the currently active table, retrieve metadata to locate the target sequence number range based on the subject identifier and time range in the query request, and perform a range query in the active table based on the sequence number range.

9. An electronic device, characterized in that, The electronic device includes a memory and a processor. The memory stores a computer program. When the processor executes the computer program, it implements the steps of the method for large-scale business data synchronization and query based on dual-table alternation and pre-paging index as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of the large-scale business data synchronization and query method based on dual-table alternation and pre-paged index as described in any one of claims 1 to 7.

Images