A large data volume paging query method and system based on ID mapping delay association

Abstract

The application relates to the technical field of big data processing, and discloses a big data volume paging query method and system based on ID mapping delay association, wherein the method comprises the following steps: acquiring a paging query request; performing parameter normalization processing on the paging query request; performing index identifier screening on a main data table; performing query condition fingerprint matching and identifier mapping preprocessing; performing delay point checking on the main data table and an associated dimension table; and performing memory association assembly according to a sub-table query result set. Compared with the prior art of directly performing multi-table JOIN query in the paging stage, especially under the condition of deep page code access and multi-dimensional associated field display, the technical problem that stable and low-transmission paging response cannot be achieved is solved. Since the current page unique identifier is acquired, delay point checking is performed based on the identifier, and application layer assembly is applied, the phased processing of paging positioning and associated data loading is realized, and the response stability of big data volume paging query is improved.

Description

Technical Field

[0001] This invention relates to the field of big data processing technology, and in particular to a method and system for large-scale pagination query based on ID mapping delayed association. Background Technology

[0002] Currently, pagination queries are a core function for handling massive data displays in internet applications, enterprise software, and data analytics platforms. With the explosive growth of data volume, traditional pagination methods based on SQL offset-limit or simple joins reveal significant performance bottlenecks when dealing with multi-table joins, complex filtering conditions, and deep pagination scenarios. For example, when a data table contains tens or even hundreds of millions of records and needs to join multiple dimension tables to obtain business information, traditional methods often require full table scans or a large number of random I / O operations, resulting in excessively long query response times, huge consumption of database server CPU and memory resources, and severely impacting user experience and system stability.

[0003] Existing technologies cannot fully meet the demands of pagination queries under high concurrency, low latency, and large data volumes. While some optimization solutions, such as using covering indexes and cursor pagination, can alleviate the problem to some extent, they either fail to adequately support complex multi-table joins and dynamic filtering conditions, or their implementation logic is complex and highly intrusive to business logic. Therefore, there is an urgent need for a new method that can achieve efficient and stable pagination query performance while maintaining query flexibility and functional integrity, in order to improve data processing efficiency and system scalability. Summary of the Invention

[0004] To address the aforementioned technical shortcomings, the purpose of this invention is to propose a large-scale pagination query method based on ID mapping delayed association. This method aims to solve the technical problem in existing technologies where multi-table JOIN queries are directly executed during the pagination stage, especially under conditions of deep page number access and multi-dimensional related field display, which cannot achieve stable and low-transfer-volume pagination response.

[0005] To solve the above technical problems, the present invention adopts the following technical solution: The present invention provides a large-scale pagination query method based on ID mapping delayed association.

[0006] The large-volume pagination query method based on ID mapping delayed association includes:

[0007] Step S10: Obtain the pagination query request, perform parameter normalization processing on the pagination query request, and obtain the pagination query description set;

[0008] Step S20: Based on the pagination query description set, perform indexed identifier filtering on the main data table to obtain the current page identifier sequence;

[0009] Step S30: Based on the current page identifier sequence, perform query condition fingerprint matching and identifier mapping preprocessing to obtain a pagination identifier mapping set;

[0010] Step S40: Based on the pagination identifier mapping set, perform delayed point lookup on the main data table and the associated dimension table to obtain the table query result set;

[0011] Step S50: Perform memory association assembly based on the table partitioning query result set to obtain the pagination query response object.

[0012] Preferably, step S10, which involves obtaining a pagination query request and performing parameter normalization on the pagination query request to obtain a pagination query description set, specifically includes:

[0013] Step S101: Receive a pagination query request from the calling end, and extract the page number parameter, page capacity parameter, sorting field, filtering conditions and field display list from the pagination query request;

[0014] Step S102: Perform field name unification, field value format conversion, null value removal, and permission condition filling on the filtering conditions to obtain standardized filtering conditions;

[0015] Step S103: Encapsulate the page number parameter, page capacity parameter, sorting field, normalized filtering conditions, and field display list to obtain a pagination query description set.

[0016] Preferably, step S20, which involves filtering the indexed identifiers of the master data table based on the pagination query description set to obtain the current page identifier sequence, specifically includes:

[0017] Step S201: Select the target composite index from the index set of the main data table according to the normalized filtering conditions and sorting fields in the pagination query description set;

[0018] Step S202: Perform an index range scan on the master data table based on the target composite index, read only the unique identifier field that satisfies the normalization filtering condition, and output the candidate identifier sequence;

[0019] Step S203: Based on the page number parameter and page capacity parameter in the pagination query description set, extract the unique identifier corresponding to the current page from the candidate identifier sequence to obtain the current page identifier sequence.

[0020] Preferably, step S202, which involves performing an index range scan on the master data table based on the target composite index, reading only the unique identifier field that satisfies the normalization filtering condition, and outputting the candidate identifier sequence, includes:

[0021] When the pagination query request carries the last sort value and the last identifier of the previous page, the last sort value and the last identifier of the previous page are used as the starting boundary of the index, and the scanning continues along the sorting direction of the target composite index to skip the repeated reading of records before the previous page.

[0022] When the pagination query request does not carry the last sort value and the last identifier of the previous page, the starting offset position is determined according to the page number parameter and the page capacity parameter, and the number of unique identifier fields corresponding to the page capacity parameter are read from the target composite index, and the candidate identifier sequence is output.

[0023] The unique identifier field is used as a basis for delayed association between the master data table and the associated dimension table in subsequent steps.

[0024] Preferably, step S30, which involves performing query condition fingerprint matching and identifier mapping preprocessing based on the current page identifier sequence to obtain a pagination identifier mapping set, specifically includes:

[0025] Step S301: Perform ordered serialization processing on the normalized filtering conditions, sorting fields, page capacity parameters, and field display list in the pagination query description set to obtain the query condition string;

[0026] Step S302: Generate a query condition fingerprint based on the query condition string, and combine the query condition fingerprint with the page number parameter to form a pagination cache key, wherein the query condition fingerprint satisfies:

[0027]

[0028] in, This represents the fingerprint of the query conditions. Represents a hash function. Indicates the filtering conditions The normalized result after field sorting, format standardization, and null value removal. Indicates the sorting field. This represents the page capacity parameter. This indicates a list of fields to be displayed. This represents a string concatenation operation;

[0029] Step S303: Query the cache database based on the pagination cache key. If a corresponding cache record exists, read the historical identifier sequence from the cache record. If no corresponding cache record exists, write the current page identifier sequence, pagination cache key, total number of records, and cache expiration time into the cache database to obtain a pagination identifier mapping set.

[0030] Preferably, step S40, which involves performing delayed point lookups on the main data table and the associated dimension table based on the pagination identifier mapping set to obtain a set of table-partitioned query results, specifically includes:

[0031] Step S401: Read the target identifier sequence from the pagination identifier mapping set, and determine the field to be queried in the main data table according to the field display list;

[0032] Step S402: Perform a batch lookup of the primary key on the master data table based on the target identifier sequence to obtain the master table record set, and extract the set of related foreign keys from the master table record set;

[0033] Step S403: Perform batch point lookup of unique indexes on at least one related dimension table based on the set of related foreign keys to obtain a set of dimension records, and encapsulate the set of main table records and the set of dimension records into a set of table-partitioned query results.

[0034] Preferably, step S50, which involves performing memory association assembly based on the partitioned query result set to obtain the paginated query response object, specifically includes:

[0035] Step S501: Using the unique identifier in the table query result set as the first mapping key, establish a main table record mapping table for the main table record set in the table query result set;

[0036] Step S502: Using the foreign key in the main table record set as the second mapping key, establish a dimension record mapping table for the dimension record set in the sub-table query result set;

[0037] Step S503: Read the main table records sequentially according to the target identifier sequence in the pagination identifier mapping set, match the corresponding dimension records from the dimension record mapping table according to the foreign keys in the main table records, and then perform field trimming and field assembly according to the field display list to obtain the pagination query response object.

[0038] This invention also provides a large-scale pagination query system based on ID mapping delayed association, comprising:

[0039] The request parsing module is used to obtain pagination query requests, perform parameter normalization processing on the pagination query requests, and obtain a pagination query description set.

[0040] The identifier filtering module is used to perform indexed identifier filtering on the main data table based on the pagination query description set to obtain the identifier sequence of the current page;

[0041] The mapping preprocessing module is used to perform query condition fingerprint matching and identifier mapping preprocessing based on the current page identifier sequence to obtain a pagination identifier mapping set;

[0042] The delayed lookup module is used to perform delayed lookups on the main data table and the associated dimension table based on the pagination identifier mapping set, and obtain a set of table query results.

[0043] The response assembly module is used to perform memory association assembly based on the table query result set to obtain a paginated query response object.

[0044] The present invention also provides a large-scale pagination query device based on ID mapping delayed association. The large-scale pagination query device based on ID mapping delayed association includes: a memory, a processor, and a large-scale pagination query program based on ID mapping delayed association stored in the memory and executable on the processor. When the large-scale pagination query program based on ID mapping delayed association is executed by the processor, it implements the above-described method.

[0045] The present invention also provides a computer program product, the computer program product including a large-scale pagination query program based on ID mapping delayed association, the large-scale pagination query program based on ID mapping delayed association implementing the above method when executed by a processor.

[0046] The beneficial effects of this invention are as follows:

[0047] 1. This invention, through the core idea of ​​"first filtering identifiers, then delaying the join," breaks down traditional one-time complex multi-table join queries into two efficient stages: First, it uses database indexes to quickly filter out the unique identifier sequence required by the main data table for the current page. Then, based on this identifier sequence, it performs batch point queries on the primary key or unique index of both the main data table and the join dimension tables. This method greatly reduces unnecessary JOIN operations and random I / O, especially in deep pagination scenarios, avoiding the performance loss caused by OFFSET and significantly improving query efficiency.

[0048] 2. This invention introduces a query condition fingerprint and identifier mapping caching mechanism. By normalizing query parameters (filtering conditions, sorting, field list, etc.) and generating unique fingerprints, the selected identifier sequences are associated with and cached with these fingerprints. For subsequent pagination requests with the same query conditions, the identifier sequences can be directly retrieved from the cache, skipping the time-consuming index scanning step, further accelerating pagination response, and effectively reducing the pressure of repeated calculations on the database. Attached Figure Description

[0049] Figure 1 This is a flowchart illustrating the first embodiment of a large-scale pagination query method based on ID mapping delayed association according to the present invention. Detailed Implementation

[0050] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments.

[0051] 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.

[0052] Example 1: As Figure 1 The diagram shown is a flowchart of a first embodiment of a large-scale pagination query method based on ID mapping delayed association according to the present invention.

[0053] In the first embodiment, the large-scale pagination query method based on ID mapping delayed association includes:

[0054] Step S10: Obtain the pagination query request, perform parameter normalization processing on the pagination query request, and obtain the pagination query description set;

[0055] The "pagination query request" in this step refers to a structured request initiated by the client or caller that contains pagination and query constraint information. "Parameter normalization" is a crucial preprocessing step, specifically including extracting core parameters such as page number, page capacity, sorting field, filtering conditions, and a list of fields to be displayed from the request. It also performs operations on the filtering conditions, such as uniformly mapping field names, standardizing value formats, removing invalid and null values, and automatically adding permission-related conditions based on security policies. The resulting "pagination query description set" is a data structure that encapsulates all normalized query parameters. It provides a unified, accurate, and secure input benchmark for all subsequent steps, ensuring the consistency and cacheability of the query logic.

[0056] This step serves as the entry point and preprocessing stage of the entire method. Its technical effect lies in standardizing and purifying query requests. By cleaning, transforming, and encapsulating the original input parameters, potentially messy, non-standard, or even security-risk query requests are transformed into standardized descriptions with clear structure, explicit semantics, and direct usability for database operations. This lays a solid foundation for subsequent efficient index-based filtering, accurate cache key generation, and secure query execution, and is a prerequisite for the efficient and stable operation of the entire process.

[0057] Compared to the traditional approach of directly concatenating user parameters into SQL statements, the normalization process in this step offers significant advantages. Traditional methods are vulnerable to SQL injection attacks and are not robust to differences in parameter format (such as capitalization, spaces, and date formats), potentially leading to index failure or query errors. This invention, through mandatory normalization, not only enhances security but also ensures that the same query logic always generates the same query description. This is crucial for subsequent optimization of query paths using indexes and for achieving efficient query result caching, representing a key step in ensuring the effectiveness of performance optimization measures from the outset.

[0058] For example, a user sends a request to query orders from the front end, with the filter conditions being "createTime>2024-01-01" and "status=1", but the field name might be "create time The date format might be "2024 / 1 / 1". Step S10 will unify these conditions to the field name "create" defined in the database. time The standard date format is "2024-01-01 00:00:00". Additionally, if the system requires that only orders from the user's department can be queried, this step will automatically add "department". id The condition "=User Department ID" is added. Ultimately, regardless of how the original input changes, the generated "pagination query description set" is completely consistent for the semantics of "querying orders in progress after 2024". This directly supports the generation of a unique cache key in step S30 and avoids cache invalidation due to minor adjustments in parameter format.

[0059] Step S20: Based on the pagination query description set, perform indexed identifier filtering on the main data table to obtain the current page identifier sequence;

[0060] In this step, the "master data table" refers to the core business data table around which the queries revolve, typically the table with the largest data volume and the highest query frequency. "Indexed identifier filtering" is one of the core operations for performance optimization in this invention. Its key lies in using database indexes (especially composite indexes) to quickly locate data, rather than scanning the entire table. Specifically, the system selects the most suitable index (target composite index) based on the normalized filtering conditions and sorting fields, and then reads only the "unique identifier field" (usually the primary key) of rows that meet the conditions through a range scan or sequential scan of the index. The "current page identifier sequence" is the list of primary key IDs belonging to the currently requested page, extracted from the entire sequence of identifiers that meet the conditions, based on the page number and page capacity. This sequence is the foundation for all subsequent join queries.

[0061] The technical advantage of this step lies in its extreme simplification of the query path. It avoids the cumbersome operations of traditional paginated queries, which involve first joining, then sorting, and finally paginating, instead employing an "index-first" strategy. By reading only the index and primary key, it avoids the large amount of random I / O caused by table lookups (i.e., finding the primary key based on the index and then reading the entire row of data from the data block), especially when the offset is large. The output "current page identifier sequence" is a lightweight list of IDs with a small data volume, facilitating in-memory transfer and processing, providing a clear target for precise point-by-point lookups in subsequent steps.

[0062] Compared to the common "SELECT...FROM A JOIN B WHERE...ORDERBY...LIMIT offset,size" pattern in existing technologies, the technical solution in this step is fundamentally different. In the traditional model, the database needs to first join multiple tables into a huge intermediate result set, then sort the entire result set, and finally skip the offset records to retrieve the size records. When the offset is large, the sorting and skipping operations are extremely costly. This invention takes the opposite approach, first using an index to quickly and cost-effectively determine "which rows are needed" (i.e., IDs) on a single table (the main data table), and completely avoiding the cost of offset through "last row of the previous page" optimization. This is equivalent to shrinking the filtering scope of complex queries from a huge joined result set to a small list of IDs, which is the fundamental reason for the performance improvement.

[0063] For example, in a scenario with 100 million order records that requires joining user and product tables, a user wants to query "page 1000 (20 records per page) where the price is greater than 100 yuan and sorted in descending order of creation time". The traditional method would require joining three tables, sorting the hundreds of millions of joined records, and then skipping the first 19980 records. Step S20 first checks if there is a composite index (price, creation time) on the order table. Using this index, the database can quickly locate the primary key IDs of all records with prices > 100 yuan, sorted in descending order of creation time. Then, it only needs to retrieve IDs from the 19981st to the 20000th (corresponding to page 1000) from this ordered list of IDs, forming a "current page identifier sequence" containing only 20 IDs. This process is almost entirely completed within the index, making it extremely fast.

[0064] Step S30: Based on the current page identifier sequence, perform query condition fingerprint matching and identifier mapping preprocessing to obtain a pagination identifier mapping set;

[0065] In this step, the "query condition fingerprint" refers to a unique, fixed-length string digest generated from the normalized query parameters (filtering conditions, sorting, page capacity, field list) obtained in step S10 using a specific algorithm (such as a hash function). It represents the "identity" of this query. "Identifier mapping preprocessing" refers to the process of associating the "current page identifier sequence" obtained in step S20 with this "query condition fingerprint" and information such as page numbers. The "pagination identifier mapping set" is the carrier of this association; it may contain information such as cache keys, identifier sequences, and the total number of records. Its core purpose is to cache the results of the time-consuming operation of "filtering IDs based on conditions," allowing different page number requests under the same query conditions to share the filtering results.

[0066] The technical advantage of this step lies in introducing an intelligent caching layer, trading space for time to further accelerate pagination queries, especially for queries not on the first page. By generating a fingerprint for each unique combination of query conditions and caching its corresponding identifier sequence (or a sequence of IDs that all meet the conditions), when a user clicks the next page, previous page, or jumps to a specific page, the system does not need to repeatedly perform the potentially time-consuming index scan and ID filtering in step S20. It only needs to retrieve the corresponding ID sequence from the cache based on the query fingerprint and page number. This greatly reduces the database load and makes the page jump response time tend to be constant, resulting in a smoother user experience.

[0067] Compared to traditional methods that cache the entire paginated query result (i.e., the complete data row containing all related fields), the caching strategy in this step is more efficient and flexible. Cacheing the complete result data volume is large, and the cache is easily invalidated if any related field in the source table is updated. This invention, however, only caches a lightweight ID sequence, resulting in a very small data volume and saving significant memory or cache database space. More importantly, the ID itself is stable; as long as the set of records in the main data table that satisfy the current query conditions remains unchanged (added, deleted, or modified), this ID sequence is valid. Even if the data in the related dimension tables changes, the accuracy of the final result can still be guaranteed because step S40 involves a delayed point query of real-time data. This "caching IDs, querying data in real-time" strategy maximizes caching benefits while ensuring data real-time performance.

[0068] For example, continuing from the previous example, a user queries a list of orders with prices greater than 100 yuan, sorted in descending order of creation time. Step S30 generates a fingerprint for this query condition. When the user first requests page 1, the system executes step S20 to obtain the ID sequence and stores this fingerprint along with the corresponding ID sequence (which could be the first N IDs or a list of all IDs that meet the condition) in the cache. When the user subsequently requests page 2, the system first generates the same fingerprint using the same query parameters, and then uses "fingerprint + page number = 2" as the key to query the cache. If a match is found, the system directly reads the ID sequence corresponding to page 2 stored in the cache (or calculates it from the full ID sequence), completely skipping the process of scanning the order table index again, significantly improving response speed.

[0069] Step S40: Based on the pagination identifier mapping set, perform delayed point lookup on the main data table and the associated dimension table to obtain the table query result set;

[0070] The "delayed point lookup" in this step is another core technical point of this invention. It refers to obtaining the precise "current page identifier sequence" (target ID list) and then querying the specific data rows corresponding to these IDs in batches through the primary key or unique index in the main data table and each related dimension table. The "table-partitioned query result set" refers to the collections that will be encapsulated from the query results of the main data table (main table record set) and the query results from each related dimension table (dimension record set), respectively. At this time, they have not yet been linked by program logic. The specific operation is as follows: First, use the target ID list to perform an "IN" query or batch single-row query (point lookup) on the main data table to obtain the main table fields; then, extract the related foreign keys (such as user ID, product ID) from these main table records, and then use these foreign key lists to perform batch point lookups on the corresponding dimension tables to obtain dimension information.

[0071] The technical advantage of this step lies in breaking down complex join queries into multiple simple, efficient batch primary key queries. Database equality queries (point lookups) on primary keys or unique indexes are extremely efficient, typically completing in constant time. Batch operations (such as using `WHERE id IN (...)`) reduce network round trips and SQL parsing overhead. Since each query is based on an exact lookup of the index, it completely avoids the performance issues that can arise from full table scans and uncontrollable JOIN operations. This "break-down" strategy ensures that even when joining multiple tables, the overall query time increases linearly and controllably, rather than deteriorating exponentially.

[0072] Compared to traditional techniques that perform table joins at the database layer, this delayed lookup step, performed at the application layer (or middleware layer), offers significant advantages. Database join operations can require complex execution plans, potentially generating temporary tables and consuming large amounts of memory and CPU, especially when the join conditions are complex or the tables are large. Delayed lookups, on the other hand, move the join logic up to the application layer, allowing the database to perform only the single-table index queries it excels at. The application layer can then leverage more flexible data structures (such as hash tables) for efficient in-memory joins (see step S50). This architecture separates computation and storage responsibilities, resulting in less pressure on the database, greater control over the application layer, and easier design of independent caching strategies for tables with different dimensions.

[0073] Step S50: Perform memory association assembly based on the table partitioning query result set to obtain the pagination query response object.

[0074] The "memory-associative assembly" in this step refers to the process of re-associating the independent "table query result set" obtained in step S40 into a complete business object according to business logic in the application's memory. The specific implementation is usually accomplished by establishing a hash map: First, the main table record set is converted into a mapping table using the unique identifier of the main data table (such as order ID) as the key; then, the record sets of each dimension are converted into mapping tables using the associated foreign key (such as user ID, product ID) as the key. Then, according to the page number order of the initial request (i.e., the order of the "current page identifier sequence"), each main table record is traversed in turn, and the associated dimension record is quickly found from the corresponding dimension mapping table according to the foreign key it carries (O(1) time complexity). Then, the required fields are selected according to the "field display list" and assembled into the "pagination query response object" finally returned to the front end.

[0075] The technical advantage of this step lies in its efficient and flexible completion of the final data integration. Leveraging the extremely fast lookup speed of in-memory hash tables, the performance overhead of join operations is negligible. Simultaneously, in-memory assembly provides great flexibility, easily handling one-to-many and many-to-many relationships (although the technical solution primarily focuses on one-to-one point lookups), and conveniently enabling dynamic field pruning (returning only the requested fields), avoiding unnecessary data transfer and serialization overhead caused by "SELECT *" in database queries. The final generated response object has a clear structure and fully conforms to the data format requirements of the front-end or the caller.

[0076] Compared to performing the join at the database layer and returning a large, fixed result set, this in-memory join at the application layer offers several advantages. First, performance is controllable; hash lookups are far more efficient than nested loop joins in certain scenarios. Second, it reduces the database load, shifting computational pressure to application servers that are easier to scale horizontally. Third, it offers high flexibility, easily integrating data from different data sources (such as databases, caches, and RPC services) for assembly, not just database joins. Fourth, it facilitates optimization; independent caching strategies can be implemented for dimensional data, such as caching frequently used, infrequently changing user information locally within the application, further accelerating the assembly process.

[0077] For example, continuing with the previous scenario, after step S40, there are three lists: order list A (20 items), user list B, and product list C. Step S50 first converts user list B into a list of users... id HashMap with keys B Product list C is converted to product id HashMap with keys C Then, it iterates through each order record in order list A according to the requested page number order (assuming it's in reverse chronological order of creation time by ID). For each order, it retrieves the user information. id From HashMap B Retrieve the corresponding user object and retrieve the product. id From HashMap C The corresponding product object is retrieved from the database. Next, based on the "field display list" sent from the front end (e.g., only order number, amount, username, and product name are required), the specified fields are extracted from these three objects and assembled into a new JSON object. After looping through 20 orders, an array containing 20 complete business objects is obtained and returned as the "pagination query response object," along with information such as the total number of pages.

[0078] Example 2: Furthermore, the present invention provides a large-scale pagination query system based on ID mapping delayed association, employing a large-scale pagination query method based on ID mapping delayed association as described in the above embodiments, which can solve the technical problem of large-scale pagination query based on ID mapping delayed association. The beneficial effects of the large-scale pagination query system based on ID mapping delayed association provided by the present invention are the same as those of the large-scale pagination query method based on ID mapping delayed association provided in the above embodiments, and other technical features of the large-scale pagination query system based on ID mapping delayed association are the same as those disclosed in the methods of the above embodiments, and will not be repeated here.

[0079] Example 3: This invention provides a large-volume pagination query device based on ID mapping delay association. The device includes: at least one processor; and a memory communicatively connected to the at least one processor. The memory stores instructions executable by the at least one processor, which, when executed, enable the at least one processor to perform the large-volume pagination query method based on ID mapping delay association described in Example 1. The large-volume pagination query device based on ID mapping delay association in this invention can include, but is not limited to, mobile terminals such as mobile phones, laptops, digital radio receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Description), PMPs (Portable Media Players), and in-vehicle terminals (e.g., in-vehicle navigation terminals), as well as fixed terminals such as digital TVs and desktop computers. This large-volume pagination query device based on ID mapping delay association is merely an example and should not limit the functionality or scope of the invention. A large-scale paging query device based on ID mapping delay association may include a processing unit (e.g., a central processing unit, a graphics processing unit, etc.) that can perform various appropriate actions and processes according to a program stored in read-only memory or a program loaded from a storage device into random access memory. The random access memory also stores various programs and data required for the operation of the large-scale paging query device based on ID mapping delay association. The processing unit, read-only memory, and random access memory are interconnected via a bus. An I / O interface is also connected to the bus. Typically, the following systems can be connected to the I / O interface: input devices including, for example, touchscreens, touchpads, keyboards, mice, image sensors, microphones, accelerometers, gyroscopes, etc.; output devices including, for example, liquid crystal displays (LCDs), speakers, vibrators, etc.; storage devices including, for example, magnetic tapes, hard disks, etc.; and communication devices. The communication device allows the large-scale paging query device based on ID mapping delay association to communicate wirelessly or wiredly with other devices to exchange data. Although the figures show a large-scale paging query device based on ID mapping delay association with various systems, it should be understood that implementation or possession of all the systems shown is not required. It can be implemented alternatively or with more or fewer systems.

[0080] Example 4: This invention also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the above-described method for large-volume pagination query based on ID mapping delayed association. The computer program product provided by this invention can solve the technical problem of large-volume pagination query based on ID mapping delayed association. Compared with the prior art, the beneficial effects of the computer program product provided by this invention are the same as those of the large-volume pagination query method based on ID mapping delayed association provided in the above embodiments, and will not be repeated here.

[0081] In particular, according to the embodiments disclosed in this invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments of this invention include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from a storage device, or installed from a read-only memory. When the computer program is executed by a processing device, it performs the functions defined in the methods of the embodiments disclosed in this invention.

[0082] It should be understood that the various parts disclosed in this invention can be implemented using hardware, software, firmware, or a combination thereof. In the description of the above embodiments, specific features, structures, materials, or characteristics can be combined in any suitable manner in one or more embodiments or examples.

[0083] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its spirit and scope. Therefore, if these modifications and variations fall within the scope of the present invention and its equivalents, the present invention also intends to include these modifications and variations.

Claims

1. A method for large-scale pagination query based on ID mapping delayed association, characterized in that, The methods include: Step S10: Obtain the pagination query request, perform parameter normalization processing on the pagination query request, and obtain the pagination query description set; Step S20: Based on the pagination query description set, perform indexed identifier filtering on the main data table to obtain the current page identifier sequence; Step S30: Based on the current page identifier sequence, perform query condition fingerprint matching and identifier mapping preprocessing to obtain a pagination identifier mapping set; wherein, the step of performing query condition fingerprint matching and identifier mapping preprocessing based on the current page identifier sequence to obtain a pagination identifier mapping set specifically includes: The standardized filtering conditions, sorting fields, page capacity parameters, and field display list in the pagination query description set are processed into an ordered serialization to obtain the query condition string; A query condition fingerprint is generated based on the query condition string, and the query condition fingerprint is combined with the page number parameter to form a pagination cache key, wherein the query condition fingerprint satisfies: ； in, This represents the fingerprint of the query conditions. Represents a hash function. Indicates the filtering conditions The normalized result after field sorting, format standardization, and null value removal. Indicates the sorting field. This represents the page capacity parameter. This indicates a list of fields to be displayed. This represents a string concatenation operation; Based on the pagination cache key, query the cache database. If a corresponding cache record exists, read the historical identifier sequence from the cache record. If no corresponding cache record exists, write the current page identifier sequence, pagination cache key, total number of records, and cache expiration time into the cache database to obtain a pagination identifier mapping set. Step S40: Based on the pagination identifier mapping set, perform delayed point lookup on the main data table and the associated dimension table to obtain the table query result set; The step of performing delayed point lookups on the main data table and related dimension tables based on the pagination identifier mapping set to obtain a pagination query result set specifically includes: Read the target identifier sequence from the pagination identifier mapping set, and determine the field to be queried in the main data table based on the field display list; Based on the target identifier sequence, perform a batch primary key lookup on the master data table to obtain the master table record set, and extract the associated foreign key set from the master table record set; Based on the set of related foreign keys, perform batch point lookup on at least one related dimension table using unique indexes to obtain a set of dimension records, and encapsulate the set of main table records and the set of dimension records into a set of table-partitioned query results. Step S50: Perform memory association assembly based on the partitioned table query result set to obtain a paginated query response object; wherein, the step of performing memory association assembly based on the partitioned table query result set to obtain a paginated query response object specifically includes: Using the unique identifier in the table partitioning query result set as the first mapping key, a main table record mapping table is established for the main table record set in the table partitioning query result set. Using the foreign key in the main table record set as the second mapping key, a dimension record mapping table is established for the dimension record set in the sub-table query result set; The main table records are read sequentially according to the target identifier sequence in the pagination identifier mapping set. The corresponding dimension records are matched from the dimension record mapping table according to the foreign keys in the main table records. Then, the fields are trimmed and assembled according to the field display list to obtain the pagination query response object.

2. The large-scale pagination query method based on ID mapping delayed association as described in claim 1, characterized in that, Step S10, which involves obtaining a pagination query request and performing parameter normalization on the pagination query request to obtain a pagination query description set, specifically includes: Step S101: Receive a pagination query request from the calling end, and extract the page number parameter, page capacity parameter, sorting field, filtering conditions and field display list from the pagination query request; Step S102: Perform field name unification, field value format conversion, null value removal, and permission condition filling on the filtering conditions to obtain standardized filtering conditions; Step S103: Encapsulate the page number parameter, page capacity parameter, sorting field, normalized filtering conditions, and field display list to obtain a pagination query description set.

3. The large-scale pagination query method based on ID mapping delayed association as described in claim 1, characterized in that, Step S20, which involves filtering the indexed identifiers of the master data table based on the pagination query description set to obtain the current page identifier sequence, specifically includes: Step S201: Select the target composite index from the index set of the main data table according to the normalized filtering conditions and sorting fields in the pagination query description set; Step S202: Perform an index range scan on the master data table based on the target composite index, read only the unique identifier field that satisfies the normalization filtering condition, and output the candidate identifier sequence; Step S203: Based on the page number parameter and page capacity parameter in the pagination query description set, extract the unique identifier corresponding to the current page from the candidate identifier sequence to obtain the current page identifier sequence.

4. The large-scale pagination query method based on ID mapping delayed association as described in claim 3, characterized in that, Step S202, which involves performing an index range scan on the master data table based on the target composite index, reading only the unique identifier field that satisfies the normalization filtering condition, and outputting the candidate identifier sequence, includes: When the pagination query request carries the last sort value and the last identifier of the previous page, the last sort value and the last identifier of the previous page are used as the starting boundary of the index, and the scanning continues along the sorting direction of the target composite index to skip the repeated reading of records before the previous page. When the pagination query request does not carry the last sort value and the last identifier of the previous page, the starting offset position is determined according to the page number parameter and the page capacity parameter, and the number of unique identifier fields corresponding to the page capacity parameter are read from the target composite index, and the candidate identifier sequence is output. The unique identifier field is used as a basis for delayed association between the master data table and the associated dimension table in subsequent steps.

5. A large-volume pagination query system based on ID mapping delayed association, applied to the large-volume pagination query method based on ID mapping delayed association as described in any one of claims 1 to 4, characterized in that, The large-scale pagination query system includes: The request parsing module is used to obtain pagination query requests, perform parameter normalization processing on the pagination query requests, and obtain a pagination query description set. The identifier filtering module is used to perform indexed identifier filtering on the main data table based on the pagination query description set to obtain the identifier sequence of the current page; The mapping preprocessing module is used to perform query condition fingerprint matching and identifier mapping preprocessing based on the current page identifier sequence to obtain a pagination identifier mapping set; wherein, the step of performing query condition fingerprint matching and identifier mapping preprocessing based on the current page identifier sequence to obtain a pagination identifier mapping set specifically includes: The standardized filtering conditions, sorting fields, page capacity parameters, and field display list in the pagination query description set are processed into an ordered serialization to obtain the query condition string; A query condition fingerprint is generated based on the query condition string, and the query condition fingerprint is combined with the page number parameter to form a pagination cache key, wherein the query condition fingerprint satisfies: ； in, This represents the fingerprint of the query conditions. Represents a hash function. Indicates the filtering conditions The normalized result after field sorting, format standardization, and null value removal. Indicates the sorting field. This represents the page capacity parameter. This indicates a list of fields to be displayed. This represents a string concatenation operation; Based on the pagination cache key, query the cache database. If a corresponding cache record exists, read the historical identifier sequence from the cache record. If no corresponding cache record exists, write the current page identifier sequence, pagination cache key, total number of records, and cache expiration time into the cache database to obtain a pagination identifier mapping set. The delayed lookup module is used to perform delayed lookups on the main data table and the associated dimension table based on the pagination identifier mapping set, and obtain a set of table query results. The step of performing delayed point lookups on the main data table and related dimension tables based on the pagination identifier mapping set to obtain a pagination query result set specifically includes: Read the target identifier sequence from the pagination identifier mapping set, and determine the field to be queried in the main data table based on the field display list; Based on the target identifier sequence, perform a batch primary key lookup on the master data table to obtain the master table record set, and extract the associated foreign key set from the master table record set; Based on the set of related foreign keys, perform batch point lookup on at least one related dimension table using unique indexes to obtain a set of dimension records, and encapsulate the set of main table records and the set of dimension records into a set of table-partitioned query results. The response assembly module is used to perform memory association assembly based on the partitioned query result set to obtain a paginated query response object; wherein the step of performing memory association assembly based on the partitioned query result set to obtain the paginated query response object specifically includes: Using the unique identifier in the table partitioning query result set as the first mapping key, a main table record mapping table is established for the main table record set in the table partitioning query result set. Using the foreign key in the main table record set as the second mapping key, a dimension record mapping table is established for the dimension record set in the sub-table query result set; The main table records are read sequentially according to the target identifier sequence in the pagination identifier mapping set. The corresponding dimension records are matched from the dimension record mapping table according to the foreign keys in the main table records. Then, the fields are trimmed and assembled according to the field display list to obtain the pagination query response object.

6. A large-scale pagination query device based on ID mapping delayed association, characterized in that, The large-volume pagination query device based on ID mapping delay association includes: a memory, a processor, and a large-volume pagination query program based on ID mapping delay association stored in the memory and executable on the processor. When the large-volume pagination query program based on ID mapping delay association is executed by the processor, it implements a large-volume pagination query method based on ID mapping delay association as described in any one of claims 1 to 4.

7. A computer program product, characterized in that, The computer program product includes a large-scale pagination query program based on ID mapping delayed association. When the large-scale pagination query program based on ID mapping delayed association is executed by the processor, it implements a large-scale pagination query method based on ID mapping delayed association as described in any one of claims 1 to 4.

Images