Concurrent query method and device for multiple data sources and data management system
By extracting database elements and conditions from query requests and using concurrent query and stream processing interfaces to sort and merge multiple databases, the problem of global sorting in multi-database queries is solved, improving the accuracy and efficiency of queries.
Patent Information
- Application Number
- CN202511723418.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-11-21
- Publication Date
- 2026-02-13
AI Technical Summary
Existing technologies struggle to achieve complete global sorting across multiple databases during large-scale data queries, leading to a decrease in the accuracy of query results.
By extracting database elements from query requests, the target database is determined, and concurrent queries are performed based on query conditions and pagination ranges. Asynchronous query threads and stream processing interfaces are used to concatenate and sort data, enabling separate sorting and merging of multiple databases.
It improves the accuracy and efficiency of queries, reduces the complexity of queries, and ensures the integrity and consistency of query results, especially performing well in high-concurrency and large-data-volume scenarios.
Smart Images
Figure CN121524218A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of big data technology, and more specifically, to a method, apparatus, computer-readable storage medium, and data management system for concurrent querying from multiple data sources. Background Technology
[0002] The accounting history query system receives data from the accounting production system daily. The massive volume of data daily, coupled with multiple re-accesses and updates of data for the same accounting date due to adjustments in accounting subsidiary periods, significantly increases the dynamism and inaccuracy of the historical query system's data. The historical system and production system have different purposes and use different databases. The historical system tends to use databases that support large-scale data storage and querying, such as GBase, while the production system prefers databases for data processing, such as TDSQL distributed databases. On the one hand, it needs to ensure that queries remain accurate throughout multiple adjustments and updates, and that sorted queries are supported; on the other hand, it also needs to ensure the timeliness of business queries while handling massive amounts of data.
[0003] In existing technologies, when a query involves multiple databases, the program first queries database A. If the number of data entries in database A is not within the user's query range or the query results are incomplete, then it continues to query database B. The results from databases A and B are then processed and returned to the user simultaneously. However, due to the large amount of data and frequent updates, it is difficult to achieve global sorting among multiple databases, resulting in a decrease in the accuracy of the query results. Summary of the Invention
[0004] The main objective of this application is to provide a concurrent query method, apparatus, computer-readable storage medium, and data management system for multiple data sources, so as to at least solve the problem in the prior art that it is difficult to achieve complete global sorting among multiple databases when querying large-scale data, which easily leads to errors in query results.
[0005] To achieve the above objectives, according to one aspect of this application, a concurrent query method for multiple data sources is provided, comprising: extracting database sharding elements from a query request, and determining at least one target database based on the database sharding elements, wherein the database sharding elements are the basis for dividing a database into sub-databases, and the target database is the sub-database corresponding to the database sharding elements; extracting query conditions from the query request, determining the number of target fields corresponding to each target database based on the query conditions, sorting the number of target fields according to the database sharding elements to obtain a statistical sequence, wherein the number of target fields is the number of fields in the target database that satisfy the query conditions; extracting pagination ranges from the query request, determining query parameters corresponding to each target database based on the pagination ranges and the statistical sequence, wherein the query parameters include the starting number of records, the number of records to be queried, and the database sharding elements; performing concurrent queries on each target database according to the query parameters to obtain multiple first target data, and concatenating each first target data according to the database sharding elements to obtain second target data.
[0006] Optionally, extracting the sharding elements from the query request and determining at least one target database based on the sharding elements includes: determining the synchronization status of the sharding elements in each of the sub-databases in the historical system; if the synchronization status is synchronized, determining the sub-database in the historical system corresponding to the sharding elements as the target database; if the synchronization status is not synchronized, determining the sub-database in the production system corresponding to the sharding elements as the target database.
[0007] Optionally, extracting the query conditions from the query request and determining the number of target fields corresponding to each target database based on the query conditions includes: constructing corresponding SQL statements based on the query conditions and each target database to obtain query statements; executing the corresponding query statements on each target database to obtain multiple target field groups, each target field group including target fields in the corresponding target database that satisfy the query conditions; and counting the number of target fields contained in each target field group to obtain the number of target fields.
[0008] Optionally, determining the query parameters corresponding to each of the target databases based on the pagination range and the statistical sequence includes: determining the total starting position and query quantity based on the pagination range to obtain a first starting position and a first query quantity; initializing the total cumulative traversal quantity to 0, the data source cumulative traversal quantity to 0, and the current query count to 0; traversing each of the target databases sequentially according to the statistical sequence, updating the total cumulative traversal quantity to the sum of the target field counts corresponding to the traversed target databases, and updating the data source cumulative traversal quantity to the target field count corresponding to the currently traversed target database; if the total cumulative traversal quantity is greater than or equal to the starting position, calculating the starting count corresponding to the currently traversed target database based on the target field count, the first starting position, and the data source cumulative quantity; adding the target field count to the current query count, and determining the target field count as the query count; if the current query count is greater than or equal to the first query quantity, the traversal ends, and the query parameters corresponding to each of the target databases are obtained.
[0009] Optionally, concurrent queries are performed on each of the target databases according to the query parameters to obtain multiple first target data, including: initiating multiple asynchronous query threads in each of the target databases, wherein each asynchronous query thread corresponds one-to-one with a target database; configuring the corresponding asynchronous query thread according to each of the query parameters; and executing the corresponding asynchronous query thread in each of the target databases to obtain the corresponding first target data.
[0010] Optionally, concatenating the first target data according to the database sub-elements to obtain the second target data includes: sorting the first target data according to the database sub-elements through a stream processing interface; and cropping and concatenating the first target data according to the pagination range to obtain the second target data.
[0011] Optionally, performing concurrent queries on each of the target databases according to the query parameters includes: if the asynchronous query thread has run for a preset time and has not received the first target data, destroying the asynchronous query thread and restarting it.
[0012] According to another aspect of this application, a concurrent query apparatus for multiple data sources is provided, comprising: a first acquisition unit, configured to extract database sharding elements from a query request and determine at least one target database based on the database sharding elements, wherein the database sharding elements are the basis for dividing a database into sub-databases, and the target database is the sub-database corresponding to the database sharding elements; a second acquisition unit, configured to extract query conditions from the query request, determine the number of target fields corresponding to each target database based on the query conditions, sort the number of target fields according to the database sharding elements to obtain a statistical sequence, wherein the number of target fields is the number of fields in the target database that satisfy the query conditions; a third acquisition unit, configured to extract pagination ranges from the query request, determine query parameters corresponding to each target database based on the pagination ranges and the statistical sequence, wherein the query parameters include the starting number of records, the number of records to be queried, and the database sharding elements; and a query unit, configured to perform concurrent queries on each target database according to the query parameters to obtain multiple first target data, and concatenate each first target data according to the database sharding elements to obtain second target data.
[0013] According to another aspect of this application, a computer-readable storage medium is provided, the computer-readable storage medium including a stored program, wherein, when the program is executed, it controls the device on which the computer-readable storage medium is located to perform any of the methods described.
[0014] According to another aspect of this application, a data management system is provided, comprising: one or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs including methods for performing any one of the methods described.
[0015] Applying the technical solution of this application, in the above-mentioned concurrent query method for multiple data sources, firstly, the database sharding elements in the query request are extracted, and at least one target database is determined based on the database sharding elements. The database sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the database sharding elements. Then, the query conditions in the query request are extracted, and the number of target fields corresponding to each target database is determined based on the query conditions. The number of target fields is sorted according to the database sharding elements to obtain a statistical sequence. The number of target fields is the number of fields in the target database that satisfy the query conditions. Afterward, the pagination range in the query request is extracted, and the query parameters corresponding to each target database are determined based on the pagination range and the statistical sequence. The query parameters include the starting number of rows, the number of rows to be queried, and the database sharding elements. Finally, concurrent queries are performed on each target database according to the query parameters to obtain multiple first target data. The first target data are then concatenated according to the database sharding elements to obtain second target data. This application identifies the target database by dividing it into database elements and pre-sorts it before querying, avoiding query result conflicts caused by data changes. Then, it queries according to the sorted results and sorts the query results afterward. It decomposes the global sorting into separate sorting for each database. Compared with traditional global query and global sorting, it reduces the complexity of the query and improves the accuracy of the query. This solves the problem in the prior art that it is difficult to achieve complete global sorting between multiple databases when querying large-scale data, which easily leads to query result errors. Attached Figure Description
[0016] Figure 1 A hardware structure block diagram of a mobile terminal for a concurrent query method for multiple data sources provided in an embodiment of this application is shown.
[0017] Figure 2 A flowchart illustrating a concurrent query method for multiple data sources provided according to an embodiment of this application is shown.
[0018] Figure 3 A structural block diagram of a concurrent query apparatus for multiple data sources provided according to an embodiment of this application is shown.
[0019] The above figures include the following reference numerals:
[0020] 102. Processor; 104. Memory; 106. Transmission device; 108. Input / output device. Detailed Implementation
[0021] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.
[0022] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.
[0023] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate for the embodiments of this application described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.
[0024] As described in the background section, in existing technologies, when a query involves multiple databases, the program first queries database A. If the number of data entries in database A is not within the user's query range or the query results are incomplete, then database B is queried. The results from databases A and B are then processed and returned to the user simultaneously. However, due to the large amount of data and frequent updates, it is difficult to achieve global sorting among multiple databases, leading to a decrease in the accuracy of the query results. To address the problem in existing technologies where it is difficult to achieve complete global sorting among multiple databases during large-scale data queries, which easily leads to errors in the query results, embodiments of this application provide a concurrent query method, apparatus, computer-readable storage medium, and data management system for multiple data sources.
[0025] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention.
[0026] The methods and embodiments provided in this application can be executed on a mobile terminal, computer terminal, or similar computing device. Taking running on a mobile terminal as an example, Figure 1 This is a hardware structure block diagram of a mobile terminal for a concurrent query method using multiple data sources, according to an embodiment of the present invention. For example... Figure 1 As shown, a mobile terminal may include one or more ( Figure 1Only one is shown in the diagram. A processor 102 (which may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.) and a memory 104 for storing data are also shown. The mobile terminal may further include a transmission device 106 for communication functions and an input / output device 108. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the mobile terminal described above. For example, the mobile terminal may also include components that are more... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.
[0027] The memory 104 can be used to store computer programs, such as application software programs and modules, like the computer program corresponding to the concurrent query method for multiple data sources in this embodiment of the invention. The processor 102 executes various functional applications and data processing by running the computer program stored in the memory 104, thereby implementing the above-described method. The memory 104 may include high-speed random access memory and non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the mobile terminal via a network. Examples of the aforementioned networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof. The transmission device 106 is used to receive or send data via a network. Specific examples of the aforementioned networks may include wireless networks provided by the mobile terminal's communication provider. In one example, the transmission device 106 includes a network interface controller (NIC), which can be connected to other network devices via a base station to communicate with the Internet. In one example, the transmission device 106 may be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.
[0028] This embodiment provides a concurrent query method for multiple data sources running on a mobile terminal, computer terminal, or similar computing device. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.
[0029] Figure 2 This is a flowchart of a concurrent query method for multiple data sources according to an embodiment of this application. For example... Figure 2 As shown, the method includes the following steps:
[0030] Step S201: Extract the sharding elements from the query request, and determine at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the sharding elements.
[0031] Understandably, in traditional multi-data source query scenarios, queries are often performed sequentially or in simple parallel fashion, i.e., querying from one data source first, and then querying from another data source based on the results or requirements. This approach can easily lead to low query efficiency when dealing with large amounts of data distributed across multiple data sources, especially when global sorting across data sources is required, which presents even greater technical challenges.
[0032] Specifically, the interface parses the query request, extracts the database elements (such as accounting dates), and dynamically determines which database to query based on the synchronization status of the accounting dates in different systems (production system A, historical system B). For example, for accounting date X, if production system A has already completed the data synchronization for that date, then only historical system B is queried; conversely, if the data is still being synchronized, then production system A must be queried simultaneously.
[0033] Step S202: Extract the query conditions from the query request, determine the number of target fields corresponding to each target database based on the query conditions, sort the number of target fields according to the database elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that meet the query conditions.
[0034] Specifically, the query conditions in the query request are extracted, and a pre-query is performed on each target database to count the number of target fields (data entries) that meet the conditions. This data will be aggregated and sorted according to the database elements to form a statistical sequence.
[0035] Step S203: Extract the pagination range from the query request, and determine the query parameters corresponding to each target database based on the pagination range and statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements.
[0036] Specifically, based on the pagination range provided by the user (e.g., the first page, displaying the first few records), the algorithm calculates specific query parameters for each data source, including the starting number of records, the number of records to be queried, and the specific values of the database elements. This step is dynamically calculated by traversing the statistical sequence to ensure that the query for each database is optimized for the pagination range.
[0037] Step S204: Perform concurrent queries on each target database according to the query parameters to obtain multiple first target data. Then, concatenate the first target data according to the database elements to obtain the second target data.
[0038] Specifically, Java's concurrent programming capabilities are used to implement concurrent queries on each target database. After the queries are completed, all the first target data (i.e., the result sets obtained from each database) will be sorted and merged according to the database elements to finally generate the second target data, which is the complete query results sorted by database elements.
[0039] In this embodiment, firstly, the database sharding elements are extracted from the query request, and at least one target database is determined based on these elements. The database sharding elements are the basis for dividing a database into sub-databases, and the target databases are the sub-databases corresponding to the database sharding elements. Next, the query conditions are extracted from the query request, and the number of target fields corresponding to each target database is determined based on these conditions. The number of target fields is then sorted according to the database sharding elements to obtain a statistical sequence. The number of target fields is the number of fields in the target database that satisfy the query conditions. Then, the pagination range is extracted from the query request, and the query parameters corresponding to each target database are determined based on the pagination range and the statistical sequence. The query parameters include the starting number of rows, the number of rows to be queried, and the database sharding elements. Finally, concurrent queries are performed on each target database according to the query parameters to obtain multiple first target data sets. These first target data sets are then concatenated according to the database sharding elements to obtain second target data sets. This application identifies the target database by dividing it into database elements and pre-sorts it before querying, avoiding query result conflicts caused by data changes. Then, it queries according to the sorted results and sorts the query results afterward. It decomposes the global sorting into separate sorting for each database. Compared with traditional global query and global sorting, it reduces the complexity of the query and improves the accuracy of the query. This solves the problem in the prior art that it is difficult to achieve complete global sorting between multiple databases when querying large-scale data, which easily leads to query result errors.
[0040] In order to determine the target data source for the data query, in one optional implementation, step S201 above includes:
[0041] Step S2011: Determine the synchronization status of the sub-database elements in each sub-database of the historical system;
[0042] Specifically, when a user initiates a query request, the system first parses the request to identify the database sharding elements, such as the accounting date. This date is the primary basis for dividing the database into sub-databases, determining which sub-database the data should be stored in. Then, the system checks the data synchronization status of that sharding element (such as a specific accounting date) in the various sub-databases of the historical system. This is because the historical system typically synchronizes data from the production system periodically, but the synchronization process may be delayed or incomplete.
[0043] Step S2012: If the synchronization status is synchronized, determine the sub-database corresponding to the sub-database element in the historical system as the target database.
[0044] Specifically, if the data synchronization status of a sub-database in the historical system for a given sub-database element (such as a specific accounting date) is "synchronized" (i.e., the data is fully replicated and available), then the system will identify the sub-database corresponding to that sub-database element in the historical system as the target database for subsequent queries.
[0045] Step S2013: If the synchronization status is not synchronized, determine the sub-database corresponding to the sub-database element in the production system as the target database.
[0046] Specifically, if the synchronization status is "not synchronized", it means that the historical system may not have the latest data or the data is incomplete. In this case, the system will turn to the sub-database corresponding to the element in the production system to query, so as to ensure the timeliness and completeness of the data.
[0047] Through the above embodiments, the system can automatically identify and adapt to the latest state of the data, thereby querying the most suitable database. This not only optimizes the query path and avoids unnecessary resource consumption, but also ensures the efficiency and accuracy of data queries, even in environments where data changes and is frequently updated.
[0048] In addition, the dynamic query strategy described above reduces the possibility of repeated data loading across systems, lowers the complexity of data processing, and also enhances the system's responsiveness, because queries can be performed directly in systems with the latest data without the need for additional data integration steps.
[0049] In order to count the number of queries for each data source, in one optional implementation, step S202 above includes:
[0050] Step S2021: Construct corresponding SQL statements based on the query conditions and each target database to obtain the query statements;
[0051] Specifically, the system extracts specific query conditions from the query request, which may include time range, product type, transaction type, etc. Based on the extracted query conditions, the system dynamically generates corresponding SQL query statements for each target database. These statements will contain database sharding elements (such as accounting dates), query conditions (such as transaction type "A"), and aggregate functions for statistical purposes (such as COUNT( )).
[0052] Step S2022: Execute the corresponding query statement for each target database to obtain multiple target field groups. The target field group includes the target fields in the corresponding target database that meet the query conditions.
[0053] Specifically, Java's concurrent programming techniques are used to execute the constructed SQL queries concurrently. For each database in both the production and historical systems, the queries will be executed simultaneously to reduce overall query wait time.
[0054] Step S2023: Count the number of target fields contained in each target field group to obtain the number of target fields.
[0055] Specifically, after executing the query statement, the system will count the COUNT(value) of each target field group (i.e., the COUNT(value) in each database query result). The number of target fields in the value is used to obtain the target field count. This provides an overview of the number of data rows that meet the criteria in each data source, offering important information for subsequent paginated concurrent queries and result sorting.
[0056] Through the above embodiments, dynamic SQL construction and concurrent execution of query statements significantly reduce the total query time, especially when handling massive amounts of data and complex query conditions, where the performance advantage is particularly evident. The statistical count of target fields provides an estimate of the data volume for subsequent concurrent queries, which helps to more accurately calculate pagination parameters and ensures the correctness and completeness of query results during final sorting and merging. The ability to dynamically construct SQL statements allows the system to adapt to various query needs, without being limited to a specific database type or structure.
[0057] In order to perform adaptive queries on various data sources, in one optional implementation, step S203 above includes:
[0058] Step S2031: Determine the total starting position and query count based on the pagination range to obtain the first starting position and the first query count;
[0059] Specifically, the system parses the required pagination range from the user's query request, which typically includes the "starting position" and the "number of queries".
[0060] Step S2032: Initialize the total cumulative traversal count to 0, the cumulative traversal count of the data source to 0, and the current query count to 0;
[0061] Specifically, to traverse the statistical sequence and calculate query parameters, the system initializes several counters: Total CumulativeCount: tracks the total number of fields traversed across all target databases; DataSourceCumulativeCount: counts the total number of fields that meet the conditions for the currently traversed target database; and CurrentQueryCount: determines the number of fields queried in the current database.
[0062] Step S2033: According to the statistical sequence, traverse each target database in sequence, update the total cumulative traversal count to the sum of the target fields corresponding to the traversed target databases, and update the cumulative traversal count of the data source to the target fields corresponding to the target database being traversed.
[0063] Specifically, starting with the first database in the statistical sequence, each database has its corresponding target field count. The target field counts of the traversed databases are accumulated to the total cumulative traversal count.
[0064] Step S2034: If the total cumulative number of traversals is greater than or equal to the starting position, calculate the starting number of the target database corresponding to the current traversal based on the number of target fields, the first starting position, and the cumulative number of data sources.
[0065] Specifically, when the total cumulative number of traversals first exceeds or equals the starting position requested by the user, the system calculates the starting number of records in the current database. The starting number of records is equal to the starting position requested by the user minus the cumulative number of fields, and then minus the number of fields in the current database.
[0066] Step S2035: Increase the current number of query results by the number of target fields, and set the number of target fields as the number of query results;
[0067] Specifically, the system increments the current query count by the target field count in the current database and sets that field count as the query count for that database.
[0068] Step S2036: If the current number of query records is greater than or equal to the number of the first query records, the traversal ends, and the query parameters corresponding to each target database are obtained.
[0069] Specifically, the iteration ends when the current number of query records reaches or exceeds the number of queries requested by the user. This means that all databases that need to be queried, along with their corresponding starting and query counts, have been determined.
[0070] The above embodiments avoid redundant queries across all data sources, retrieving only data within the user's pagination range, significantly improving query speed. Dynamically calculating query parameters for each data source ensures the consistency and completeness of the final query results, even if the data is distributed across multiple databases. The reduced response time and guaranteed data accuracy directly enhance the user experience, especially in high-concurrency and large-volume query scenarios. It adapts to different database types and data distribution patterns, providing a universal solution for complex multi-data source queries.
[0071] To improve query efficiency, in one optional implementation, step S204 includes:
[0072] Step S2041: Initiate multiple asynchronous query threads in each target database, with each asynchronous query thread corresponding to a target database.
[0073] Specifically, a corresponding asynchronous query thread is created for each target database. For example, if the target database contains production system A and historical system B, two asynchronous query threads, ThreadA and ThreadB, will be created, corresponding to the two data sources respectively.
[0074] Step S2042: Configure the corresponding asynchronous query thread according to each query parameter;
[0075] Specifically, each asynchronous query thread needs to be configured with specific query parameters, including but not limited to the pagination start position, the number of records to query, and the specific values for database elements. Using the query parameters determined in the previous step, the system can accurately guide each query thread on how to execute the query operation, ensuring the accuracy and consistency of the results.
[0076] Step S2043: Execute the corresponding asynchronous query thread in each target database to obtain the corresponding first target data.
[0077] Specifically, asynchronous query threads are deployed to their respective target databases to execute predefined query operations. Thanks to concurrency technology, even complex queries executed simultaneously across multiple databases can be completed in a relatively short time. Each query thread will return a set of data, which will be referred to as the first target data.
[0078] Through the above embodiments, concurrent query execution avoids the waiting time associated with serial queries, significantly shortening query response time, especially in environments with massive data volumes or multiple databases. More queries can be processed simultaneously, improving the system's concurrency capabilities and thus increasing throughput. Because the queries are carefully designed based on pagination ranges and statistical sequences, unnecessary data retrieval is avoided, reducing system resource consumption.
[0079] In order to unify the query results from various data sources, in an optional implementation, step S204 above further includes:
[0080] Step S2044: Sort each first target data according to the database elements through the stream processing interface;
[0081] Specifically, the integrated data is sorted using a stream processing interface, based on database sharding elements (such as accounting dates). In Java, the `Stream.sorted()` method can be used in conjunction with a custom comparator to sort the data based on these database sharding elements.
[0082] Step S2045: The first target data is cropped and spliced according to the pagination range to obtain the second target data.
[0083] Specifically, based on the pagination range requested by the user, a specific range of data is cropped from the sorted result set. This step ensures that the user can only obtain data within the requested range, improving the efficiency and targeting of data processing. The cropped data will then be concatenated into the final query result, i.e., the second target data. This step may involve converting the data into a specific format, such as JSON or XML, for front-end display or subsequent processing.
[0084] Through the above embodiments, a unified sorting and pruning logic ensures the consistency and coherence of concurrent query results from multiple data sources in the final display, avoiding data fragmentation and disorder. Sorting and pruning operations are performed after all concurrent queries have completed, reducing user waiting time and improving query response speed. Processing only data within the scope of the user's request avoids processing redundant data, reducing system resource consumption and improving data processing efficiency. The use of stream processing interfaces and pagination mechanisms makes the code more concise and easier to maintain, while easily adapting to future changes in data sources or query requirements.
[0085] To avoid query timeout, in an optional implementation, step S2043 includes:
[0086] Step S20431: If the asynchronous query thread has run for a preset time and has not received the first target data, destroy the asynchronous query thread and restart it.
[0087] Specifically, to prevent query threads from waiting indefinitely, the system sets a preset timeout for each thread, such as 5 minutes. If a thread fails to return a result within the preset time, the system will destroy the thread and release the system resources it occupies. Using Java's CompletableFuture or other similar concurrency tools, a timeout listener can be set for each asynchronous query thread to monitor the thread's status and execution time. When it is detected that the execution of a thread exceeds the preset timeout, the system will trigger the thread's destruction operation and release related resources, including the database connection and thread context. Once a thread is destroyed due to a timeout, the system will decide whether to restart the thread to perform the query based on business needs and the system's operating status. The restart strategy may depend on various factors, such as the importance of the query, the database load, and the overall resource allocation of the system.
[0088] It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and although a logical order is shown in the flowchart, in some cases the steps shown or described may be executed in a different order than that shown here.
[0089] This application also provides a concurrent query device for multiple data sources. It should be noted that this concurrent query device for multiple data sources can be used to execute the concurrent query method for multiple data sources provided in this application. This device is used to implement the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can refer to a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.
[0090] The following describes the concurrent query device for multiple data sources provided in the embodiments of this application.
[0091] Figure 3 This is a structural block diagram of a concurrent query device for multiple data sources according to an embodiment of this application. For example... Figure 3 As shown, the device includes:
[0092] The first acquisition unit 10 is used to extract the sharding elements in the query request and determine at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the sharding elements.
[0093] The second acquisition unit 20 is used to extract the query conditions in the query request, determine the number of target fields corresponding to each target database according to the query conditions, sort the number of target fields according to the database elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that meet the query conditions.
[0094] The third acquisition unit 30 is used to extract the pagination range in the query request and determine the query parameters corresponding to each target database based on the pagination range and statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements.
[0095] The query unit 40 is used to perform concurrent queries on each target database according to the query parameters to obtain multiple first target data, and to concatenate each first target data according to the database elements to obtain second target data.
[0096] In this embodiment, the first acquisition unit extracts the database sharding elements from the query request and determines at least one target database based on the database sharding elements. The database sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the database sharding elements. The second acquisition unit extracts the query conditions from the query request, determines the number of target fields corresponding to each target database based on the query conditions, sorts the number of target fields according to the database sharding elements, and obtains a statistical sequence. The number of target fields is the number of fields in the target database that satisfy the query conditions. The third acquisition unit extracts the pagination range from the query request, determines the query parameters corresponding to each target database based on the pagination range and the statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database sharding elements. The query unit performs concurrent queries on each target database according to the query parameters to obtain multiple first target data. The first target data is then concatenated according to the database sharding elements to obtain second target data. This application identifies the target database by dividing it into database elements and pre-sorts it before querying, avoiding query result conflicts caused by data changes. Then, it queries according to the sorted results and sorts the query results afterward. It decomposes the global sorting into separate sorting for each database. Compared with traditional global query and global sorting, it reduces the complexity of the query and improves the accuracy of the query. This solves the problem in the prior art that it is difficult to achieve complete global sorting between multiple databases when querying large-scale data, which easily leads to query result errors.
[0097] In order to determine the target data source for the data query, in one optional implementation, the first acquisition unit includes:
[0098] The first determination module is used to determine the synchronization status of the sub-database elements in the historical system.
[0099] The second determination module is used to determine the sub-database corresponding to the sub-database element in the historical system as the target database when the synchronization status is synchronized.
[0100] The third determination module is used to determine the sub-database corresponding to the sub-database element in the production system as the target database when the synchronization status is not synchronized.
[0101] In order to count the number of queries from each data source, in one optional implementation, the second acquisition unit includes:
[0102] The construction module is used to construct the corresponding SQL statements based on the query conditions and each target database to obtain the query statement;
[0103] The first processing module is used to execute the corresponding query statements on each target database to obtain multiple target field groups, which include the target fields in the corresponding target database that meet the query conditions.
[0104] The second processing module is used to count the number of target fields contained in each target field group, and obtain the number of target fields.
[0105] In order to perform adaptive queries on various data sources, in one optional implementation, the third acquisition unit mentioned above includes:
[0106] The fourth determining module is used to determine the total starting position and query count based on the pagination range, and to obtain the first starting position and the first query count;
[0107] The third processing module is used to initialize the total cumulative traversal count to 0, the cumulative traversal count of the data source to 0, and the current query count to 0;
[0108] The fourth processing module is used to traverse each target database sequentially according to the statistical sequence, update the total cumulative traversal count to the sum of the target fields corresponding to the traversed target databases, and update the cumulative traversal count of the data source to the target fields corresponding to the currently traversed target database.
[0109] The calculation module is used to calculate the starting number of the target database being traversed, based on the number of target fields, the first starting position, and the cumulative number of data sources, when the total cumulative number of traversals is greater than or equal to the starting position.
[0110] The fifth processing module is used to add the target field number to the current query count and determine the target field number as the query count;
[0111] The sixth processing module is used to end the traversal and obtain the query parameters corresponding to each target database when the current number of query records is greater than or equal to the number of queries in the first query.
[0112] To improve query efficiency, in one optional implementation, the query unit includes:
[0113] The module calls multiple asynchronous query threads in each target database, with each asynchronous query thread corresponding to a target database.
[0114] The configuration module is used to configure the corresponding asynchronous query thread according to each query parameter;
[0115] The execution module is used to execute the corresponding asynchronous query thread in each target database to obtain the corresponding first target data.
[0116] In order to unify the query results from various data sources, in one optional implementation, the query unit further includes:
[0117] The seventh processing module is used to sort the first target data according to the database elements through the stream processing interface;
[0118] The eighth processing module is used to crop and splice the first target data according to the pagination range to obtain the second target data.
[0119] To avoid query timeouts, in one optional implementation, the execution module includes:
[0120] The Destroy submodule is used to destroy and restart the asynchronous query thread if it has run for a preset time without receiving the first target data.
[0121] The aforementioned concurrent query device for multiple data sources includes a processor and a memory. The first acquisition unit, second acquisition unit, third acquisition unit, and query unit are all stored as program units in the memory, and the processor executes these program units to achieve the corresponding functions. All of the above modules reside in the same processor; alternatively, the modules may be located in different processors in any combination.
[0122] The processor contains a kernel, which retrieves the corresponding program units from memory. One or more kernels can be configured, and adjusting kernel parameters can improve the efficiency and accuracy of data retrieval.
[0123] The memory may include non-permanent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.
[0124] This invention provides a computer-readable storage medium including a stored program, wherein, when the program is executed, it controls the device where the computer-readable storage medium is located to execute the concurrent query method for multiple data sources.
[0125] Specifically, concurrent query methods for multiple data sources include:
[0126] Step S201: Extract the sharding elements from the query request, and determine at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the sharding elements.
[0127] Step S202: Extract the query conditions from the query request, determine the number of target fields corresponding to each target database based on the query conditions, sort the number of target fields according to the database elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that meet the query conditions.
[0128] Step S203: Extract the pagination range from the query request, and determine the query parameters corresponding to each target database based on the pagination range and statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements.
[0129] Step S204: Perform concurrent queries on each target database according to the query parameters to obtain multiple first target data. Then, concatenate the first target data according to the database elements to obtain the second target data.
[0130] This invention provides a processor for running a program, wherein the program executes the concurrent query method for multiple data sources.
[0131] Specifically, concurrent query methods for multiple data sources include:
[0132] Step S201: Extract the sharding elements from the query request, and determine at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the sharding elements.
[0133] Step S202: Extract the query conditions from the query request, determine the number of target fields corresponding to each target database based on the query conditions, sort the number of target fields according to the database elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that meet the query conditions.
[0134] Step S203: Extract the pagination range from the query request, and determine the query parameters corresponding to each target database based on the pagination range and statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements.
[0135] Step S204: Perform concurrent queries on each target database according to the query parameters to obtain multiple first target data. Then, concatenate the first target data according to the database elements to obtain the second target data.
[0136] This invention provides a data management system, which includes a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, it performs at least the following steps:
[0137] Step S201: Extract the sharding elements from the query request, and determine at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the sharding elements.
[0138] Step S202: Extract the query conditions from the query request, determine the number of target fields corresponding to each target database based on the query conditions, sort the number of target fields according to the database elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that meet the query conditions.
[0139] Step S203: Extract the pagination range from the query request, and determine the query parameters corresponding to each target database based on the pagination range and statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements.
[0140] Step S204: Perform concurrent queries on each target database according to the query parameters to obtain multiple first target data. Then, concatenate the first target data according to the database elements to obtain the second target data.
[0141] This application also provides a computer program product, which, when executed on a data processing device, is suitable for executing an initialization program having at least the following method steps:
[0142] Step S201: Extract the sharding elements from the query request, and determine at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the sharding elements.
[0143] Step S202: Extract the query conditions from the query request, determine the number of target fields corresponding to each target database based on the query conditions, sort the number of target fields according to the database elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that meet the query conditions.
[0144] Step S203: Extract the pagination range from the query request, and determine the query parameters corresponding to each target database based on the pagination range and statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements.
[0145] Step S204: Perform concurrent queries on each target database according to the query parameters to obtain multiple first target data. Then, concatenate the first target data according to the database elements to obtain the second target data.
[0146] It is obvious to those skilled in the art that the modules or steps of the present invention described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. They can be implemented using computer-executable program code, and thus can be stored in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those described herein, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular combination of hardware and software.
[0147] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0148] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0149] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0150] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0151] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.
[0152] Memory may include non-persistent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.
[0153] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information by any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic tape, disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.
[0154] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.
[0155] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.
[0156] As can be seen from the above description, the embodiments of this application achieve the following technical effects:
[0157] 1) The concurrent query method for multiple data sources in this application firstly extracts the sharding elements from the query request and determines at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target databases are the sub-databases corresponding to the sharding elements. Then, the query conditions are extracted from the query request, and the number of target fields corresponding to each target database is determined based on the query conditions. The number of target fields is sorted according to the sharding elements to obtain a statistical sequence. The number of target fields is the number of fields in the target database that satisfy the query conditions. Afterward, the pagination range is extracted from the query request, and the query parameters corresponding to each target database are determined based on the pagination range and the statistical sequence. The query parameters include the starting number of rows, the number of rows to be queried, and the sharding elements. Finally, concurrent queries are performed on each target database according to the query parameters to obtain multiple first target data. The first target data are then concatenated according to the sharding elements to obtain second target data. This application identifies the target database by dividing it into database elements and pre-sorts it before querying, avoiding query result conflicts caused by data changes. Then, it queries according to the sorted results and sorts the query results afterward. It decomposes the global sorting into separate sorting for each database. Compared with traditional global query and global sorting, it reduces the complexity of the query and improves the accuracy of the query. This solves the problem in the prior art that it is difficult to achieve complete global sorting between multiple databases when querying large-scale data, which easily leads to query result errors.
[0158] 2) The concurrent query device for multiple data sources of this application comprises: a first acquisition unit extracting database sharding elements from the query request and determining at least one target database based on the database sharding elements, where the database sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the database sharding elements; a second acquisition unit extracting query conditions from the query request and determining the number of target fields corresponding to each target database based on the query conditions, sorting the number of target fields according to the database sharding elements to obtain a statistical sequence, where the number of target fields is the number of fields in the target database that satisfy the query conditions; a third acquisition unit extracting the pagination range from the query request and determining the query parameters corresponding to each target database based on the pagination range and the statistical sequence, where the query parameters include the starting number of rows, the number of rows to be queried, and the database sharding elements; and a query unit performing concurrent queries on each target database according to the query parameters to obtain multiple first target data, concatenating the first target data according to the database sharding elements to obtain second target data. This application identifies the target database by dividing it into database elements and pre-sorts it before querying, avoiding query result conflicts caused by data changes. Then, it queries according to the sorted results and sorts the query results afterward. It decomposes the global sorting into separate sorting for each database. Compared with traditional global query and global sorting, it reduces the complexity of the query and improves the accuracy of the query. This solves the problem in the prior art that it is difficult to achieve complete global sorting between multiple databases when querying large-scale data, which easily leads to query result errors.
[0159] The above description is merely a preferred embodiment of this application and is not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.
Claims
1. A concurrent query method for multiple data sources, characterized in that, include: Extract the sharding elements from the query request, and determine at least one target database based on the sharding elements. The sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the sharding elements. Extract the query conditions from the query request, determine the number of target fields corresponding to each target database based on the query conditions, sort the number of target fields according to the database elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that satisfy the query conditions. Extract the pagination range from the query request, and determine the query parameters corresponding to each target database based on the pagination range and the statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements. Concurrent queries are performed on each of the target databases according to the query parameters to obtain multiple first target data. The first target data are then concatenated according to the database sub-elements to obtain second target data.
2. The method according to claim 1, characterized in that, Extracting database sharding elements from the query request and determining at least one target database based on the database sharding elements, including: Determine the synchronization status of the database elements in each of the sub-databases of the historical system; If the synchronization status is synchronized, the sub-database corresponding to the sub-database element in the historical system is determined as the target database; If the synchronization status is not synchronized, the sub-database corresponding to the sub-database element in the production system is determined as the target database.
3. The method according to claim 1, characterized in that, Extracting the query conditions from the query request, and determining the number of target fields corresponding to each target database based on the query conditions, including: Based on the query conditions and each of the target databases, construct the corresponding SQL statements to obtain the query statements; Execute the corresponding query statement on each of the target databases to obtain multiple target field groups, each target field group including the target fields in the corresponding target database that satisfy the query conditions; The number of target fields contained in each target field group is counted to obtain the number of target fields.
4. The method according to claim 1, characterized in that, The query parameters corresponding to each target database are determined based on the pagination range and the statistical sequence, including: The total starting position and number of queries are determined based on the pagination range, thus obtaining the first starting position and the first number of queries; Initialize the total cumulative traversal count to 0, the cumulative traversal count of the data source to 0, and the current query count to 0; According to the statistical sequence, each target database is traversed sequentially, and the total cumulative traversal count is updated to the sum of the target field counts corresponding to the traversed target databases. The cumulative traversal count of the data source is updated to the target field count corresponding to the currently traversed target database. If the total cumulative number of traversals is greater than or equal to the starting position, the starting number of records corresponding to the target database being traversed is calculated based on the number of target fields, the first starting position, and the cumulative number of data sources. The current number of query results is increased by the number of target fields, and the number of target fields is determined as the number of query results. If the current number of queries is greater than or equal to the first number of queries, the traversal ends, and the query parameters corresponding to each target database are obtained.
5. The method according to claim 1, characterized in that, Concurrent queries are performed on each of the target databases based on the query parameters to obtain multiple first target data, including: Multiple asynchronous query threads are invoked in each of the target databases, and each asynchronous query thread corresponds one-to-one with the target database. Configure the corresponding asynchronous query thread according to each of the query parameters; The corresponding asynchronous query thread is executed in each of the target databases to obtain the corresponding first target data.
6. The method according to claim 1, characterized in that, The first target data is concatenated according to the aforementioned database elements to obtain the second target data, including: The first target data is sorted according to the database elements through the stream processing interface; The first target data is cropped and spliced according to the pagination range to obtain the second target data.
7. The method according to claim 5, characterized in that, Concurrent queries are performed on each of the target databases based on the query parameters, including: If the asynchronous query thread runs for a preset period of time without receiving the first target data, the asynchronous query thread is destroyed and restarted.
8. A concurrent query device with multiple data sources, characterized in that, The device includes: The first acquisition unit is used to extract the database sharding elements from the query request and determine at least one target database based on the database sharding elements. The database sharding elements are the basis for dividing the database into sub-databases, and the target database is the sub-database corresponding to the database sharding elements. The second acquisition unit is used to extract the query conditions from the query request, determine the number of target fields corresponding to each target database according to the query conditions, sort the number of target fields according to the database sub-elements, and obtain a statistical sequence. The number of target fields is the number of fields in the target database that satisfy the query conditions. The third acquisition unit is used to extract the pagination range in the query request, and determine the query parameters corresponding to each target database based on the pagination range and the statistical sequence. The query parameters include the starting number of records, the number of records to be queried, and the database elements. The query unit is used to perform concurrent queries on each of the target databases according to the query parameters to obtain multiple first target data, and to concatenate each of the first target data according to the database sub-elements to obtain second target data.
9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored program, wherein, when the program is executed, it controls the device on which the computer-readable storage medium is located to perform the method according to any one of claims 1 to 7.
10. A data management system, characterized in that, include: One or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs comprising methods for performing any one of claims 1 to 7.