Data retrieval methods, systems and related products
By synchronizing a portion of data from a relational database to a non-relational database and utilizing the non-relational database for external retrieval services, the problem of slow query speed and low accuracy of relational databases under large data volumes or complex data structures is solved, achieving more efficient and accurate data retrieval.
Patent Information
- Application Number
- CN202410868630.4
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-06-28
- Publication Date
- 2026-11-10
- Estimated Expiration
- 2044-06-28
AI Technical Summary
Existing relational databases suffer from slow query speed and low accuracy when performing fuzzy queries on large amounts of data or complex data structures, which negatively impacts user experience.
Synchronize some data from a relational database to a non-relational database, use the non-relational database for external retrieval services, narrow the query scope through the non-relational database, and perform precise queries in the relational database.
It improves the efficiency and accuracy of data retrieval feedback and reduces the pressure on relational databases when faced with a large number of concurrent query requests.
Smart Images

Figure CN118820293B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of data technology, and in particular to data retrieval methods, systems and related products. Background Technology
[0002] In existing digital information systems, it is often necessary to query information in business data records. For example, fuzzy queries can be performed on text fields such as summaries and remarks in data records such as transaction logs, vouchers, and business documents, or on numerical fields such as transaction amounts and dates.
[0003] The current query solution involves directly using a relational database such as MySQL to perform fuzzy matching queries on the fields specified by the user. This requires creating full-text indexes for each field in the relational database to find relevant information for that field and return it to the user. However, when the database contains a large amount of data or has a complex data structure, numerous full-text index fields are created. In this case, querying each full-text index field one by one becomes very slow. Furthermore, it's easy to misinterpret and hit too many full-text index fields, resulting in numerous and inaccurate query results, negatively impacting the user experience. Summary of the Invention
[0004] This application provides data retrieval methods, systems, and related products for quickly retrieving data records containing the content of a search field, narrowing the query scope for relational databases, and improving the efficiency and accuracy of retrieval results.
[0005] The first aspect of this application provides a data retrieval method, including:
[0006] Get data query request;
[0007] When the data query request contains a field of a preset type to be queried, the data query request is executed in the non-relational database to find the first data content corresponding to the field of the field to be queried in the non-relational database; the non-relational database is synchronized with the data content of at least some data records in the relational database, and the data content contains the content corresponding to the field of the preset type;
[0008] Obtain the target record identifier; the target record identifier refers to the identifier of the data record containing the first data content;
[0009] The target record identifier and the data query request are combined as a new query request, and the new query request is executed in the data record of the relational database to find the second data content corresponding to the field to be queried in the relational database as the retrieval result.
[0010] Optionally, before executing the data query request in the non-relational database to find the first data content corresponding to the field to be queried in the non-relational database, the method further includes:
[0011] Monitor the content changes of the relational database to capture new data records generated by the changes in the relational database;
[0012] Distinguish between historically shared content and non-historically shared content in the new data records; the historically shared content refers to repetitive data content that was previously stored in other data records in the relational database.
[0013] The non-historical shared content is synchronously transmitted to the non-relational database, while the historical shared content is refused to be transmitted to the non-relational database, in order to construct the non-relational database.
[0014] Optionally, distinguishing historical common content in the new data record includes:
[0015] Based on the association between the data content of each data record in the relational database and the version iteration number of each data record, the original data content in the data records whose version iteration number exceeds the initial version number is determined as historical common content.
[0016] Optionally, obtaining the target record identifier includes:
[0017] Obtain the association between each data content and the identifier of each data record in the relational database; the association is used to indicate the data record where the data content is located, and each data record includes a new data record that has changed;
[0018] Based on the aforementioned association, the identifier of the data record containing the first data content is determined as the target record identifier.
[0019] Optionally, before executing the new query request in the data records of the relational database, the method further includes:
[0020] The data content belonging to the preset type in the non-relational database and the relational database is distinguished by attributes;
[0021] Based on the attributes of the field to be searched, a search range is narrowed down in the non-relational database and the relational database to find the first data content and the second data content respectively within the search range; the search range includes data content with the same attributes as the field to be searched.
[0022] Optionally, after executing the data query request in the non-relational database to find the first data content corresponding to the field to be queried in the non-relational database, the method further includes:
[0023] If the quantity of the first data content or the second data content does not meet the preset display quantity, determine the storage location of the first data content in the non-relational database;
[0024] The search scope of the field to be searched in the non-relational database is expanded from the page where the storage location is located to other pages in the non-relational database, and the data query request is executed on the other pages until the first data content that meets the preset display quantity is obtained or the page turning ends.
[0025] Optionally, after retrieving the second data content corresponding to the field to be searched in the relational database as the retrieval result, the method further includes:
[0026] If there is at least one set of first data content corresponding to the field to be queried, each set of first data content and the second data content corresponding to the first data content shall be regarded as a comparison group.
[0027] Select a preset number of comparison groups to compare the consistency of the content within each group, so as to filter out the comparison groups with inconsistent content within each group.
[0028] Based on the inconsistencies in the second data content within the comparison group, the first data content is synchronously updated as a new search result.
[0029] In practice, the method described in the first aspect of this application may be implemented using the content described in the second aspect of this application.
[0030] A second aspect of this application provides a data retrieval system, including: an acquisition unit and a processing unit;
[0031] The acquisition unit is used to acquire data query requests;
[0032] The processing unit is configured to, when the data query request contains a field to be queried of a preset type, execute the data query request in a non-relational database to find the first data content corresponding to the field to be queried in the non-relational database; the non-relational database synchronously contains data content of at least some data records in the relational database, and the data content contains the content corresponding to the field of the preset type;
[0033] The acquisition unit is further configured to acquire a target record identifier; the target record identifier refers to the identifier of the data record containing the first data content;
[0034] The processing unit is further configured to combine the target record identifier and the data query request as a new query request, and execute the new query request in the data record of the relational database to find the second data content corresponding to the field to be queried in the relational database as the retrieval result.
[0035] A third aspect of this application provides an electronic device, including: a processor and a memory;
[0036] The processor is configured to communicate with the memory and execute instructions in the memory to implement the method described in the first aspect of the embodiments of this application or any specific implementation thereof.
[0037] A fourth aspect of this application provides a computer-readable storage medium storing computer instructions that, when executed by a processor, implement the method described in the first aspect or any specific implementation thereof.
[0038] A fifth aspect of this application provides a computer program product, the computer program product including computer instructions, which, when executed by a processor, implement the method described in the first aspect of this application or any specific implementation thereof.
[0039] As can be seen from the above technical solutions, the embodiments of this application have at least the following advantages:
[0040] This application embodiment synchronously stores at least a portion of the content from a relational database into a non-relational database, which then performs external retrieval services. This reduces the query pressure on the relational database when faced with a large number of concurrent data query requests. Furthermore, searching in the non-relational database provides search direction and narrows the search scope for the relational database query. For example, it may indicate that the second data content can be found within the data record containing the first data content, thereby improving the efficiency and accuracy of the search results. Attached Figure Description
[0041] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings.
[0042] It should be noted that although the steps in the flowcharts (if any) involved in the embodiments are drawn sequentially according to the arrows, unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts involved in the embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps.
[0043] Figure 1 This is a schematic diagram of the system architecture of the data retrieval method according to an embodiment of this application;
[0044] Figures 2 to 4 This is a flowchart illustrating the data retrieval method according to an embodiment of this application;
[0045] Figure 5 This is a schematic diagram of the structure of a data retrieval system according to an embodiment of this application;
[0046] Figure 6 This is a schematic diagram of the structure of an electronic device according to an embodiment of this application. Detailed Implementation
[0047] To make the objectives, technical solutions, and advantages of this application clearer, the application will be further described in detail below with reference to the accompanying drawings. The described embodiments should not be regarded as limitations on this application. All other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0048] The terms “first,” “second,” “third,” “fourth,” etc. (if present) in the specification, claims, and drawings of this application are used to distinguish similar objects and are not necessarily used to describe a particular order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms “comprising” and “having,” and any variations thereof, are intended to cover a 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.
[0049] In the following description, expressions such as "one specific implementation" or "one specific example" describe a subset of all possible embodiments. However, it is understood that "one specific implementation" or "one specific example" can be the same or different subset of all possible embodiments and can be combined with each other without conflict. In the following description, the term "multiple" means at least two. When a certain value mentioned in this application reaches a threshold (if it exists), in some specific examples, it may include the former being greater than the latter. When "any" or "at least one" or similar expressions are mentioned, it specifically refers to any one of the listed examples or any combination of these examples.
[0050] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein is for the purpose of describing embodiments of this application only and is not intended to limit this application.
[0051] The method provided in this application embodiment can be applied to, for example, Figure 1 In the illustrated application environment, terminal 102 communicates with server 101 via a network. Data storage system 100 can store relational databases, non-relational databases associated with server 101, and data that needs to be processed. Data storage system 100 can be integrated onto server 101 or located in the cloud or on other network servers. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, and portable wearable devices. Portable wearable devices can include smartwatches, smart bracelets, head-mounted devices, etc. Server 101 can be implemented using a standalone server or a server cluster consisting of multiple servers.
[0052] Terminal 102 can obtain data query requests input by the user. These requests may include query statements such as "find the summary of the tax return list and the tax amount submitted by department xx in 2024". Terminal 102 can send the data query request to server 101. Server 101 can determine whether the request contains a field of a preset type, such as a non-numeric text field. It should be noted that this preset type can be determined based on the content type of the field to be queried. For example, if the content of the field to be queried is numeric content such as "amount" or "time", then the field to be queried can be considered to be a numeric field. Furthermore, if the content of the field to be queried is mainly plain text such as Chinese characters or English, such as detailed text descriptions of a summary (mainly plain text but may also contain numeric content), then the field to be queried can be considered to be a non-numeric (or text) field.
[0053] If the request contains a predefined field to be queried (or query condition), such as "tax return summary," a data query request can be executed in a non-relational database to find the first data content (or first query result) corresponding to the field to be queried in the non-relational database. Then, based on the identifier (such as ID number) of the data record containing the first data content and the data query request, the second data content (or second query result) can be quickly retrieved in a relational database. Finally, the first data content can be synchronously updated based on the second data content and returned to terminal 102 to prevent any omissions in the query results returned by the first data content, which could affect the accuracy of the query.
[0054] Of course, it is also possible to directly return the second data content to terminal 102 without synchronously updating the first data content. It should be noted that the method provided in this application embodiment can be implemented jointly by the terminal device and the server as described above, or it can be implemented entirely on the server side, or it can be implemented entirely on the terminal device side. The specific implementation can be determined according to the actual application scenario, and no restrictions are imposed here.
[0055] The method described in this application will be further explained in detail below.
[0056] Please see Figure 2 The first aspect of this application provides a specific embodiment of a data retrieval method, which includes the following operation steps:
[0057] Step S21: Obtain the data query request.
[0058] The relational database in this application embodiment can be a Structured Query Language database (MySQL) or an Oracle database, which can be referred to as a business database or source database. The non-relational database can also be referred to as a business database or source database, such as a search engine (ES, Elasticsearch) or NoSQL database. A non-relational database can be referred to as an external database relative to a relational database. The data content of the aforementioned data records can primarily be text-based content such as summaries or notes in transaction logs, vouchers, and business documents, or it can be numerical content such as amounts and times; that is, the aforementioned preset types can be text-based and / or numerical, and the specific type can be determined according to the actual situation, and is not limited here.
[0059] Step S22: When the data query request contains a field of a preset type to be queried, execute the data query request in the non-relational database to find the first data content corresponding to the field to be queried in the non-relational database.
[0060] The aforementioned non-relational database synchronizes with at least some data records in the relational database, and the data content includes the content corresponding to fields belonging to a preset type.
[0061] like Figure 1 As illustrated by the example, relational databases, such as MySQL, often have less than ideal retrieval results for text-based content due to limitations in their structure and syntax. To effectively retrieve the text content needed by users, it is necessary to reconstruct the relational database or significantly recompile its syntax (including changing the syntax of query statements), resulting in high costs. Furthermore, the inventors have discovered that non-relational databases, such as Elasticsearch (ES), can quickly determine whether the database contains content corresponding to the searched field. In other words, they can quickly locate the content corresponding to the searched field, unlike MySQL's full-text index which requires traversing each word segmentation. This allows for rapid narrowing of the search scope and faster retrieval. Therefore, the inventors propose uploading at least a portion of the data content from MySQL to non-relational databases such as ES. This portion of data content can specifically be the text content from the data records to be retrieved, leveraging ES's fast index to quickly provide query results and improve the user experience.
[0062] For example, when a data query is initiated, the system can first parse whether the data query request contains a pre-defined type of field to be queried; that is, it can first determine whether the indexing conditions or filtering conditions for enabling external indexing services such as Elasticsearch (ES) queries are met. When the non-relational database is an ES database, the search in step S22 can be simply referred to as the ES query process. Furthermore, external indexing services such as ES queries can only be enabled when the data query request contains a pre-defined type of field to be queried, so as to avoid increasing the query pressure on ES by calling external indexing services every time.
[0063] Of course, without considering the data storage and query pressure of non-relational databases, in some examples, the entire data content of the relational database can be synchronously stored in a non-relational database represented by Elasticsearch, so that every data query request uses the Elasticsearch query channel, thereby quickly providing query results.
[0064] For example, if the default data type is text, and the query request contains a query statement (or query condition): "Find the summary of the tax return list and the tax amount submitted by department xx in 2024", then it does indeed contain the default data type field "Tax Return List Summary". Therefore, the Elasticsearch (ES) query process can be used to find the text content details corresponding to "Tax Return List Summary" in the ES database: "This tax return list records the tax amount, submitting personnel, etc., of department xx in March 2024". This is the first data content. The content corresponding to "Tax Amount" is a numeric value and can be classified as a non-text value. Of course, the ES query process can also be used to find the numeric content corresponding to "Tax Amount" in the ES database.
[0065] Step S23: Obtain the target record identifier.
[0066] The target record identifier refers to the identifier of the data record containing the first data content. As in the example above, this target record identifier can be the ID of the tax return form containing the first data content. Since each piece of text content in the database can be regarded as a record by the registration mark, the ID of the tax return form can be collectively referred to as the registration text record ID, such as T1001, T1002, etc.
[0067] Step S24: Combine the target record identifier and the data query request as a new query request, and execute the new query request in the data records of the relational database to find the second data content corresponding to the field to be queried in the relational database as the retrieval result.
[0068] When the relational database is MySQL, step S24 can be simply referred to as the SQL query process. As explained above, the target record identifier can be considered a query hint for the SQL query process, used to reduce the search scope of the SQL query. For example, it specifically indicates that the second data content can be found in the data record identified as the target record identifier, without wasting time and computing power searching for other data records in MySQL. It can also be seen that the aforementioned "first data content" is an intermediate result when performing a full-text search. Its purpose is to elicit the truly needed search result, "second data content." Of course, it is also possible to choose to use the first data content as the desired search result, as detailed below, which will not be elaborated here.
[0069] In summary, this embodiment of the application synchronously stores a portion of the content from the relational database into a non-relational database, which then performs external retrieval services. This reduces the query pressure on the relational database when faced with a large number of concurrent data query requests. Furthermore, searching in the non-relational database can provide search direction and narrow the search scope for the relational database, such as indicating that the second data content can be found within the data record containing the first data content, thereby improving the efficiency and accuracy of the search results.
[0070] Based on the examples above, some specific possible implementation examples will be provided below. In practical applications, the implementation content of these examples can be combined or implemented separately as needed according to the corresponding functional principles and application logic. If combined, the execution order between the combined examples can be determined according to their respective processing logic, which can be determined by the actual scenario.
[0071] based on Figure 2 For example content, please refer to Figures 3 to 4 This application provides another specific embodiment of a data retrieval method, which includes the following operation steps:
[0072] As a possible implementation, before step S24, the method of this application embodiment may further include the following operations (partitioning and grouping data content): distinguishing the attributes of data content belonging to a preset type in the non-relational database and the relational database; locking the search range in the non-relational database and the relational database based on the attributes of the field to be searched, so as to find the first data content and the second data content in the search range respectively; the search range includes data content with the same attributes as the field to be searched.
[0073] Specifically, data within the database can be categorized by attributes, such as organization, time period, or associated personnel. For example, data belonging to the Finance Department could be tagged "Finance Department," and data from 2024 could be tagged "2024." The specific categorization is not limited. Using attribute information (or partitioning and grouping information) can improve Elasticsearch's query efficiency and matching accuracy, while reducing the number of Elasticsearch records that do not meet the criteria.
[0074] Data partitioning and grouping are considered because when a user's data query request includes partitioning and grouping information for the field to be queried, this partitioning and grouping information, along with the filtering conditions in the request, can be used to query Elasticsearch. Furthermore, since the data partitioning and grouping values that can be queried and processed, and the number of partitioning and grouping dimensions, are generally less than the threshold for the required number of query results, the query scope can be narrowed. This significantly reduces the scope of Elasticsearch's retrieval and improves its retrieval efficiency. For example:
[0075] If a user's query is simply written as "[Summary of Tax Returns for Organization xx]", then directly using the query condition "[Summary of Tax Returns for Organization xx]" to search Elasticsearch will result in a relatively large search scope. However, if the user's query includes the required attribute information such as "[Organization xx]" and "[June]", then this attribute information can be used as an identifier for data partitioning and grouping in Elasticsearch. This reduces the search scope and improves the efficiency and speed of Elasticsearch queries.
[0076] As explained above, these attributes can be used to provide query direction for query requests and new query requests, thereby narrowing the search scope of the field to be queried in non-relational databases and relational databases and improving query efficiency.
[0077] Step S21: Obtain the data query request.
[0078] In some specific examples, prior to step S22, the embodiments of this application may also include the following operations (constructing a non-relational database): monitoring changes in the content of the relational database to capture new data records generated by changes in the relational database; distinguishing between historically shared content and non-historically shared content in the new data records; historically shared content refers to duplicate content that was previously stored in other data records in the non-relational database; synchronously transmitting non-historically shared content to the non-relational database, and refusing to transmit historically shared content to the non-relational database, thereby constructing a non-relational database.
[0079] The execution order of the above-mentioned process of building a non-relational database and step S21 is not limited, and they can be executed simultaneously.
[0080] In practical applications, data transmission can be achieved through data synchronization suites. External full-text search service libraries such as Elasticsearch (ES) or NoSQL can be used as external index libraries for the business database (i.e., relational database). Furthermore, new, modified, and deleted business data can be synchronized to the ES query system (i.e., the ES database) through scheduled or event-triggered mechanisms. Generally, only the content of records that need to be retrieved (text-based) can be synchronized to the ES database to avoid ES bearing the full query load.
[0081] Considering that text records in business data may be duplicated, such as summary details and remarks details in multiple versions of tax return forms, which may use the same few lines of text description (i.e., there is historically shared content), while the differences may be a few lines of summary content and a few lines of remarks details newly added based on the previous version of the tax return form (i.e., there is non-historically shared content), in order to avoid duplicate information uploaded to the Elasticsearch database, occupying the storage space of the Elasticsearch database, and wasting resources caused by repeatedly uploading the same information, if historically shared content is uploaded to the Elasticsearch database again, only the non-historically shared content can be synchronously transmitted to the non-relational database to avoid increasing the storage pressure on the Elasticsearch database.
[0082] As one possible implementation, the process of "monitoring changes in the content of a relational database to capture new data records generated by those changes" can specifically include the following operations (using an event-triggered mechanism): setting event-triggered commands for the relational database so that when changes in the database's content are detected, new data records generated by those changes are captured. Specifically, when business data changes, the data records generated by the changes can be captured through event-triggered methods such as database triggers, operation services, and database transaction logs.
[0083] In practical applications, the established relationships (tables) can be used to distinguish historically shared content in new data records. Furthermore, these relationships (tables) can be used to deduplicate the identified historically shared content. In addition to data transmission, the aforementioned data synchronization suite can also handle the creation, updating, inspection, storage, and use of relationships (tables).
[0084] In some specific examples, the process of "distinguishing historical common content in new data records" can specifically include the following operations: based on the association between the data content of each data record in the relational database and the version iteration number of each data record, the original data content in the data records whose version iteration number exceeds the initial version number is identified as historical common content; the version iteration number indicates the number of times the data record has been generated.
[0085] like Figure 4 As shown, each business data record (or source record) can be a document record such as an expense report or transaction log, containing text content. For ease of explanation and understanding, the version iteration number (or version number) of each data record in the relational database can be simply used as the identifier of each data record, i.e., version iteration number = data record identifier ID, to construct the relationship (table), for example:
[0086] There is an expense reimbursement list submitted yesterday. The version number of this expense reimbursement list can be registered as the initial version number 1001 of the business data record (which can be attributed to a document). The summary content in this version of the expense reimbursement list can be registered as the initial version number T1001 of the text record (which can be attributed to content details). If a piece of text content is added today based on this summary content, then the version number of this version of the expense reimbursement list can be registered as 1002. The original summary content with the T1001 number in this version of the expense reimbursement list still shares the original registered version number T1001. This can be understood as the same text content sharing a common identifier T1001, but the count has been accumulated to indicate how many times it has appeared so far. This count can be simply replaced by the latest version number of the data record, such as the version number 1002 mentioned above. It can represent the version number of the reimbursement list generated by the change, or it can represent the original content in the reimbursement list, such as the summary content of T1001, which is historically common content and has appeared twice so far - because the version number 1002 has accumulated more than the initial version number 1001.
[0087] In this way, it is possible to construct something like Figure 4 The relationship table shown, in short, records the association between text content uploaded to Elasticsearch and the actual source business data. Specifically, it indicates which text content appears in which source data records and how many times it appears. Text content that appears multiple times can be called historical shared content. If historical shared content has been synchronized to the Elasticsearch database, this part of the text content that is about to be transmitted or has already been transmitted can be removed to avoid duplicate storage in the Elasticsearch database.
[0088] Step S22: When the data query request contains a field of a preset type to be queried, execute the data query request in the non-relational database to find the first data content corresponding to the field to be queried in the non-relational database.
[0089] Step S23: Obtain the target record identifier.
[0090] In some specific examples, step S23 may include the following operations: obtaining the association relationship between each data content in the relational database and the identifier of each data record; the association relationship is used to indicate the data record where the data content is located, and each data record contains a new data record that has changed; based on the association relationship, the identifier of the data record containing the first data content is determined as the target record identifier.
[0091] As mentioned above Figure 4The example illustrates how the association table [Registered Text Record ID] can be used for a secondary lookup of the [Source Business Record ID]. By examining the association table, the data record (version 1001, version 1002) containing the first data content (T1001) can be identified. The identifier of this data record (e.g., source business record IDs 1001 and 1002) can then be used as the target record identifier. In other words, after the ES query branch finds the specified data content, the association relationship can be used to find which source records also contain that data content.
[0092] As one possible implementation, if there are few or no duplicate records in the current actual business, the above-mentioned relationship (table) can be ignored and the [Business Data Record ID] and [Text content that needs to be searched for in full text, such as summary, remarks, etc.] in the source business record can be directly uploaded to the ES database. This way, querying the ES database can directly obtain the ID of the source business record, eliminating the need for a secondary lookup of the [Source Business Record ID] using the [Registered Text Record ID] through the relationship table as described above.
[0093] Step S24: Combine the target record identifier and the data query request as a new query request, and execute the new query request in the data records of the relational database to find the second data content corresponding to the field to be queried in the relational database as the retrieval result.
[0094] In short, the embodiments of this application can: reduce the data storage space of full-text indexes, achieve accurate matching during queries; meet complex business query scenarios, such as querying text and content other than numerical values; do not require major modifications to some complex business systems; and significantly improve the query efficiency of full-text retrieval in complex scenarios.
[0095] In some examples, the first data content can be selected as the search result. However, considering that the content between databases may differ due to synchronization time differences, in order to avoid inaccuracies such as omissions when the first data content is used as the search result, the accuracy and reliability of the ES query result (i.e. the first data content) can be verified based on the second data content obtained from the SQL query process, thereby completing the process of checking and filling in the gaps in the first data content.
[0096] If the accuracy of the ES query results is found to be unsatisfactory, such as inconsistencies between the first and second data contents (including cases where the first data contents are incomplete), the first data contents can be synchronously updated based on the second data contents. This is because, in reality, the source database has a more comprehensive data content than an external database (which receives data synchronously from the source database), and is more timely and reliable. Therefore, as a possible implementation, after step S24, the method of this application embodiment may further include the following operation (selecting at least a portion of the first data contents as the search result): If there is at least one set of first data contents corresponding to the field to be searched, each set of first data contents and the second data contents corresponding to the first data contents are respectively used as a comparison group; a preset number of comparison groups are selected to compare the consistency of the contents within each group, thereby filtering out comparison groups with inconsistent contents; based on the second data contents in the comparison groups with inconsistent contents, the first data contents are synchronously updated as a new search result.
[0097] The reason why there are multiple sets of first data content corresponding to the field to be queried is that there are multiple target record identifiers. If so, the first data content and its second data content corresponding to each target record identifier can be used as a comparison group to complete the checking and filling of gaps in the first data content.
[0098] Elasticsearch (ES) query results may be inaccurate or fail to meet the filtering conditions of the final SQL query. Therefore, it is necessary to verify the final results in conjunction with the SQL query. Furthermore, the amount of data that meets the conditions may be large each time a query is performed. Therefore, it is only necessary to obtain and verify the required number of records, rather than verifying all the first data content that is matched in ES. For example, the second data content, which can be directly retrieved from the text record ID of the first data content, can be used as the comparison object for the first data content. Thus, the first and second data contents form a comparison group. Further, to avoid verifying too many records at once, the paginated results of the ES query can be verified in batches or paginated manner. That is, the consistency comparison between the contents within each group can be performed in batches. Of course, the consistency between the contents within each group can also be compared simultaneously; the specific method is not limited.
[0099] As a possible implementation, after step S22, the method of this application embodiment may further include the following operations (outputting more query results): if the quantity of the first data content or the second data content does not meet the preset display quantity, determine the storage location of the first data content in the non-relational database; flip the search range of the field to be searched in the non-relational database from the page where the storage location is located to other pages in the non-relational database, and execute the data query request on other pages until the first data content that meets the preset display quantity is obtained or the page flipping ends.
[0100] like Figure 3 As shown, S1: When business data is added or changed, the changed data records can be captured through event triggering methods such as database triggers, operation services, and database transaction logs. S2: With the help of the data synchronization suite, the text information generated by the change is checked and processed, as well as the relationship between the text and the source record; with the help of the data synchronization suite, the text information is synchronously stored in the ES service cluster.
[0101] When a data query request is initiated, the first step (Step-1) is to parse the existing query conditions, i.e., the specific query statement (including the fields to be queried), to determine whether there are fields of the preset type. The query conditions may or may not contain attribute information of the fields to be queried, which can be entered by the user.
[0102] If the query conditions do not contain text search criteria that meet the retrieval requirements, i.e. there are no text fields to be searched, since only numeric fields such as amount or date are to be searched, then there is no need to go through the ES query (Step-2), because the SQL query can do a good job. That is, the original SQL query logic can be executed directly (Step-4) and the results can be output (Step-6).
[0103] If the query conditions include text retrieval conditions, first perform an ES query (Step-2) to obtain the ES query results (i.e., the first data content). Then, (Step-3) combine the ES query results with the original query conditions to perform an SQL query (Step-4) to obtain the SQL query results (i.e., the second data content).
[0104] It can be determined whether the first or second data content meets the final display quantity requirement (Step-5). If it does, the result is output (Step-6). If the final display quantity requirement is not met, the next page of ES is switched (i.e., pagination) and the process is repeated (Step 2, Step 3, Step 4, Step 5). That is, steps 23 to 25 above are repeated: first, an ES query is performed to obtain the ID of the matched text record, and then the text record ID is used in conjunction with the original query conditions to perform an SQL query in the business database until the data content record that meets all conditions (including meeting the display quantity requirement) is finally output (Step-6), or the ES pagination ends.
[0105] In summary, as Figure 4As shown, the embodiments of this application can: (1) extract [text information], [grouping and partitioning] dimension attribute information, etc. from the source data records of the relational database and synchronously upload them to non-relational databases such as ES for storage; when adding text, the efficiency of deduplication of duplicate text content can be improved based on the table index field corresponding to the text content; the data fields contained in each record in the ES database can be: 1. registered text record ID, 2. text record (e.g., summary details, remarks details), 3. partition grouping value (not necessarily required). (2) The data synchronization suite can record the association between the deduplicated text content and the source data records to form an association table. (3) In ES, the [text information] can be quickly queried using the attribute information of the [grouping and partitioning] dimension (which is not present when the user does not input) and the original query conditions input by the user, and returned. (4) The source business data record ID corresponding to the text ID can be found using the association table. (5) The business data table can be re-queried using the source business data record ID plus the original query conditions, i.e., SQL query, to obtain more accurate and complete data content as query results.
[0106] As can be seen from the above description, the advantages of the embodiments of this application are:
[0107] 1. Storing the relationship information between text and data records in a relational database, and using the index fields of the relationship table, can ensure the efficiency of checking for duplicate text when adding new relationship records.
[0108] 2. It is not required to synchronize all business data. Only the searchable text information needs to be synchronized to the query service system represented by ES to avoid wasting resources.
[0109] 2. Not tied to a specific full-text search service. For example, you can use a full-text search service similar to Elasticsearch.
[0110] 3. It is perfectly compatible with existing business systems that use SQL queries. The external index service is only activated when full-text search is needed, i.e., an Elasticsearch query is used. When full-text search is not used, the original query logic and services are still used, thus having minimal impact on existing systems. It automatically selects between direct SQL query or accelerated external index query based on whether full-text search is required in the input query conditions, offering high selectivity.
[0111] 4. It simultaneously satisfies both efficient full-text query and accurate fuzzy text matching. When directly using Elasticsearch (ES) for queries, ES may perform word segmentation on the text conditions, leading to incorrect query results. However, in this embodiment, the original SQL is used to validate and filter the first set of data before the final data output, thus ensuring that the query results are closer to 100% accurate.
[0112] 5. By combining SQL with external full-text search services, we can improve the query efficiency of fuzzy matching of text in business data while ensuring accurate matching.
[0113] 6. Perform paginated queries on external full-text search services based on the number of query results to avoid redundant and invalid queries.
[0114] 7. Using data partitioning and grouping information can ensure the query efficiency of text data in Elasticsearch and reduce the number of Elasticsearch records that do not meet the conditions.
[0115] Compared to Figure 2 The examples shown illustrate that the additional or refined examples or possible implementation methods described above may not necessarily be executed in actual implementation. If more than two examples or possible implementation methods are added or refined, these examples or possible implementation methods can be implemented in combination or individually. If implemented in combination, the execution order between the combined examples can be determined according to their respective processing logic, depending on the actual scenario.
[0116] Please see Figure 5 The second aspect of this application provides a specific embodiment of a data retrieval system, which includes: an acquisition unit 501 and a processing unit 502;
[0117] The acquisition unit 501 is used to acquire data query requests;
[0118] The processing unit 502 is used to execute the data query request in the non-relational database when the data query request contains a field of a preset type to be queried, so as to find the first data content corresponding to the field of the field to be queried in the non-relational database; the non-relational database synchronizes with the data content of at least some data records in the relational database, and the data content contains the content corresponding to the field of the preset type;
[0119] The acquisition unit 501 is further configured to acquire a target record identifier; the target record identifier refers to the identifier of the data record containing the first data content;
[0120] Processing unit 502 is further configured to combine the target record identifier and the data query request as a new query request, and execute the new query request in the data records of the relational database to find the second data content corresponding to the field to be queried in the relational database as the retrieval result.
[0121] In some examples, the processing unit 502 is also used to: monitor changes in the content of the relational database in order to capture new data records generated by the changes in the relational database;
[0122] Distinguish between historically shared content and non-historically shared content in new data records; historically shared content refers to repetitive data content that has been stored in other data records in a relational database.
[0123] Non-historical shared content is synchronously transferred to a non-relational database, while historical shared content is refused to be transferred to the non-relational database, in order to construct a non-relational database.
[0124] In some examples, the acquisition unit 501 is specifically used to: determine the original data content in data records whose version iteration number exceeds the initial version number as historical common content based on the association between the data content of each data record in the relational database and the version iteration number of each data record; the version iteration number represents the number of times the data record has been generated.
[0125] In some examples, obtaining unit 501 is specifically used for:
[0126] Retrieves the association between each data content and the identifier of each data record in a relational database; the association is used to indicate the data record where the data content is located, and each data record contains new data records that have changed;
[0127] Based on the association relationship, the identifier of the data record containing the first data content is determined as the target record identifier.
[0128] In some examples, the processing unit 502 is also used to: distinguish attributes of data content belonging to a preset type in non-relational databases and relational databases;
[0129] Based on the attributes of the field to be searched, the search range is narrowed down in both non-relational and relational databases to find the first and second data contents respectively within the search range; the search range includes data contents with the same attributes as the field to be searched.
[0130] In some examples, processing unit 502 is also used for:
[0131] If the quantity of the first data content or the second data content does not meet the preset display quantity, determine the storage location of the first data content in the non-relational database;
[0132] The search scope of the field to be searched in the non-relational database is paginated from the page where the storage location is located to other pages in the non-relational database, and the data query request is executed on other pages until the first set of data content that meets the preset display quantity is obtained or the pagination ends.
[0133] In some examples, processing unit 502 is also used for:
[0134] If there is at least one set of first data content corresponding to the field to be queried, each set of first data content and the second data content corresponding to the first data content shall be regarded as a comparison group.
[0135] Select a preset number of comparison groups to compare the consistency of content within each group, in order to filter out comparison groups with inconsistent content.
[0136] Based on inconsistencies in the content within a group, the second data content in the comparison group is synchronously updated as the new search result.
[0137] In this embodiment, the operations performed by each unit of the data retrieval system are similar to those described in the first aspect or any specific method embodiment of the first aspect, and will not be repeated here. Of course, the specific implementation process of each operation in the first aspect of this application can also be found in the relevant description of the second aspect.
[0138] Please see Figure 6 The electronic device 600 of this application embodiment may include one or more processors (such as central processing units, CPUs) 601 and memory 605, wherein the memory 605 stores one or more applications or data.
[0139] The memory 605 can be volatile or persistent storage. The program stored in the memory 605 can include one or more modules, each module including a series of instruction operations on the electronic device. Furthermore, the processor 601 can be configured to communicate with the memory 605 and execute the series of instruction operations stored in the memory 605 on the electronic device 600.
[0140] Electronic device 600 may also include one or more power supplies 602, one or more wired or wireless network interfaces 603, one or more input / output interfaces 604, and / or one or more operating systems, such as Windows Server™, Mac OS X™, Unix™, Linux™, FreeBSD™, etc.
[0141] The processor 601 can perform the operations performed by the first aspect or any specific method embodiment of the first aspect, which will not be described in detail here.
[0142] This application provides a computer-readable storage medium including instructions that, when executed on a computer, cause the computer to perform the method as described in the first aspect or any specific implementation thereof.
[0143] This application provides a computer program product containing instructions or computer programs, which, when run on a computer, causes the computer to perform the method described in the first aspect or any specific implementation thereof.
[0144] It is understood that, in the various embodiments of this application, the sequence number of each step does not imply the order of execution. The execution order of each step should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application. The operations added or refined to the above-described methods, systems, or devices (if any) may not necessarily be executed in specific implementations. If more than two operations are added, these operations can be implemented in combination or individually, depending on the actual scenario.
[0145] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working process of the system (if it exists) and device described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.
[0146] In the several embodiments provided in this application, it should be understood that the disclosed apparatus and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system or apparatus, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between apparatuses or units may be electrical, mechanical, or other forms.
[0147] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.
[0148] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.
[0149] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product (or computer program product) is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, a business server, or a network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.
Claims
1. A data retrieval method, characterized in that, include: Get data query request; When the data query request contains a field of a preset type to be queried, the data query request is executed in a non-relational database to find the first data content corresponding to the field to be queried in the non-relational database; The non-relational database synchronizes data content from at least some data records in the relational database, and the data content includes the content corresponding to the fields of the preset type; Record the association between the data content synchronously transmitted to the non-relational database and the data records containing the data content in the relational database; Obtaining the target record identifier includes: determining the identifier of the data record containing the first data content in the relational database as the target record identifier based on the association relationship; the target record identifier refers to the identifier of the data record containing the first data content; The target record identifier and the data query request are combined as a new query request, and the new query request is executed in the data record of the relational database to find the second data content corresponding to the field to be queried in the relational database as the retrieval result.
2. The data retrieval method according to claim 1, characterized in that, Before executing the data query request in the non-relational database to find the first data content corresponding to the field to be queried in the non-relational database, the method further includes: Monitor the content changes of the relational database to capture new data records generated by the changes in the relational database; Distinguish between historically shared content and non-historically shared content in the new data records; the historically shared content refers to repetitive data content that was previously stored in other data records in the relational database. The non-historical shared content is synchronously transmitted to the non-relational database, while the historical shared content is refused to be transmitted to the non-relational database, in order to construct the non-relational database.
3. The data retrieval method according to claim 2, characterized in that, The process of distinguishing historical common content in the new data records includes: Based on the association between the data content of each data record in the relational database and the version iteration number of each data record, the original data content in the data records whose version iteration number exceeds the initial version number is determined as historical common content; the version iteration number represents the number of times the data record has been generated.
4. The data retrieval method according to claim 1, characterized in that, The association is used to indicate the data record in which the data content is located, and each of the data records contains new data records that have changed.
5. The data retrieval method according to claim 1, characterized in that, Before executing the new query request in the data records of the relational database, the method further includes: The data content belonging to the preset type in the non-relational database and the relational database is distinguished by attributes; Based on the attributes of the field to be searched, a search range is narrowed down in the non-relational database and the relational database to find the first data content and the second data content respectively within the search range; the search range includes data content with the same attributes as the field to be searched.
6. The data retrieval method according to claim 1, characterized in that, After executing the data query request in the non-relational database to find the first data content corresponding to the field to be queried in the non-relational database, the method further includes: If the quantity of the first data content or the second data content does not meet the preset display quantity, determine the storage location of the first data content in the non-relational database; The search scope of the field to be searched in the non-relational database is expanded from the page where the storage location is located to other pages in the non-relational database, and the data query request is executed on the other pages until the first data content that meets the preset display quantity is obtained or the page turning ends.
7. The data retrieval method according to claim 1, characterized in that, After retrieving the second data content corresponding to the field to be searched in the relational database as the retrieval result, the method further includes: If there is at least one set of first data content corresponding to the field to be queried, each set of first data content and the second data content corresponding to the first data content shall be regarded as a comparison group. Select a preset number of comparison groups to compare the consistency of the content within each group, so as to filter out the comparison groups with inconsistent content within each group. Based on the inconsistencies in the second data content within the comparison group, the first data content is synchronously updated as a new search result.
8. A data retrieval system, characterized in that, include: Acquisition unit, processing unit, and recording module; The acquisition unit is used to acquire data query requests; The processing unit is configured to, when the data query request contains a field to be queried of a preset type, execute the data query request in a non-relational database to find the first data content corresponding to the field to be queried in the non-relational database; the non-relational database synchronously contains data content of at least some data records in the relational database, and the data content contains the content corresponding to the field of the preset type; The recording module is used to record the association between the data content synchronously transmitted to the non-relational database and the data records containing the data content in the relational database; The acquisition unit is further configured to acquire a target record identifier, including: determining the identifier of the data record containing the first data content in the relational database as the target record identifier based on the association relationship; The target record identifier refers to the identifier of the data record that contains the first data content; The processing unit is further configured to combine the target record identifier and the data query request as a new query request, and execute the new query request in the data record of the relational database to find the second data content corresponding to the field to be queried in the relational database as the retrieval result.
9. An electronic device, characterized in that, include: Processor and memory; The processor is configured to communicate with the memory and execute instructions in the memory to implement the method of any one of claims 1 to 7.
10. A readable storage medium, characterized in that, The readable storage medium stores computer instructions that, when executed by a processor, implement the method as described in any one of claims 1 to 7.
11. A computer program product, characterized in that, The computer program product includes computer instructions that, when executed by a processor, implement the method as described in any one of claims 1 to 7.
Citation Information
Patent Citations
MySQL large table association query system and method
CN115905313A
Data query method and device, computer equipment and storage medium
CN116610703A
