A method for improving data retrieval speed

Abstract

The present application belongs to the technical field of data retrieval, and particularly relates to a method for improving data retrieval speed, comprising the following steps: S1, generating a multi-table fuzzy query request; S2, building a business physical view service; S3, dimension reduction of the multi-table fuzzy query request, and single-table query of the business physical view; wherein the business physical view is classified into a single physical view for the same type of business function. The present application establishes a business physical view service, creates and updates the business physical view to be queried in real time, and performs full-text indexing on the business physical view, so as to provide single-physical-view fast fuzzy query for business query, make the fuzzy query not limited by left and right fuzzy queries, and greatly improve the query efficiency from tens of seconds or even hundreds of seconds to tens of milliseconds, thereby greatly improving the user experience, especially for multi-table joint query and multi-field simultaneous query, which more embodies the query advantage.

Description

TECHNICAL FIELD

[0001] The present application belongs to the technical field of data retrieval, and particularly relates to a method for improving data retrieval speed. BACKGROUND

[0002] In the development process of a TMS system or a CRM system using a MySQL relational database, a customer often needs to perform fuzzy query on certain information. For example, a query input box is provided on a small program order list interface, and all orders corresponding to an order number or a mobile phone number or a license plate number or an address containing the character "21" need to be found in the query input box. Such a query often involves multiple business tables, such as an order table containing an order number, an account table containing a mobile phone number, an order capacity table containing a license plate number, and a goods source table containing address information, and a multi-table joint multi-field fuzzy query needs to be performed. When the data volume of various tables is relatively large, such a query is very slow, and an interface timeout often occurs due to a long data query time, resulting in that a function cannot be normally used. Meanwhile, in a high concurrency case, a query can cause a sharp decrease in database performance.

[0003] In order to avoid the above-mentioned slow query, most of the current methods utilize the features of MySQL. In order to ensure that an index can be utilized, left fuzzy query is prohibited in development, and only right fuzzy query can be performed. Or a search engine system, such as ES, is utilized to perform full-text search. However, both of the two methods have disadvantages. Prohibiting left fuzzy query can result in that a starting character cannot be known in an actual scenario, and thus a query cannot be performed. The full-text search of ES is not suitable for a case where not all field data of a TMS or a CRM system is pure text.

[0004] Therefore, in view of the above-mentioned technical problems, it is necessary to provide a method for improving data retrieval speed. SUMMARY

[0005] The present application aims to provide a method for improving data retrieval speed, so as to solve the problems of slow and low-efficiency multi-table joint fuzzy query data.

[0006] In order to achieve the above-mentioned purpose, a technical scheme provided by an embodiment of the present application is as follows.

[0007] A method for improving data retrieval speed, comprising the following steps:

[0008] S1, generating a multi-table fuzzy query request;

[0009] S2, building a business physical view service;

[0010] S3, dimensionally reducing the multi-table fuzzy query request, and performing single-table query on the business physical view;

[0011] The business physical view is classified into a single physical view for the same type of business function, and the data stored in the business physical view is subjected to business logic processing before storage to obtain data available for direct query.

[0012] Further, the business physical view service in S2 includes a probe service, a Kafka cache service, and physical view processing logic. The probe service is used as a slave database of a MySQL database to monitor changes in the master database in real time and send the collected change data to the Kafka service for caching. The probe service can well solve the synchronization of business physical view data and business table data, avoid the problem that the business table data changes but the business physical view table data is not updated in time, and does not need to modify the logic of the original business system, so that the business system and the business physical view service are decoupled, facilitating development and expansion.

[0013] The Kafka cache service is used to store the business table change data obtained through the probe service. The high throughput and high cache of the Kafka cache service can ensure that the data is not lost. If the physical view processing logic service as the consumer end is restarted, the cached data will not be lost, and after the restart, the consumption can continue from the consumption point before the restart, ensuring that the change data of the business table can be completely provided to the physical view processing logic for processing.

[0014] Further, the physical view processing logic includes the following operation steps:

[0015] S201, classifying the business query result, and establishing a business physical view table for each type of query result to determine the type of business physical view table to be established;

[0016] S202, determining the fields corresponding to each business physical view table after business classification;

[0017] S203, if the result of the fuzzy query contains multiple fields, a summary field is added to the business physical view table, and the summary values of the multiple fields are stored;

[0018] S204, full-text indexes are established for the fields and summary fields of the business physical view, respectively;

[0019] S205, updating the business physical view data in real time;

[0020] S206, data processing by the business processing logic;

[0021] S207, saving the processed data to the business physical view table through the business physical view update module.

[0022] Further, the business physical view table in S202 is established on the basis of multiple business data tables, is abstracted from the fields involved according to the filtering conditions of each business table, and is a subset of the data items of each business table.

[0023] Further, the design principles of the business physical view table in S202 are as follows:

[0024] S2021, a main Key is determined for the same type of business, and a corresponding business physical view table is established.

[0025] S2022, the fields corresponding to each filtering condition of the same type of business are extracted from the corresponding business table as the fields of the business physical view table.

[0026] Further, the MySQL in S204 supports a field-level full-text index of 2 characters by default, and requires at least 2 characters to be input in the user query interface to perform a query. If single-character fuzzy query needs to be supported, the configuration in MySQL needs to be modified to support single-character fuzzy query.

[0027] Further, because the summary field is data generated by combining multiple field data, in order to enable the full-text index to be used for the summary field, special processing must be performed on the composition of the summary field content, that is, the summary field value in S204 = field 1 value + space + field 2 value + space + … + space + field N value. Spaces are added between each field value to prevent the query result value from being divided into two fields when the summary field full-text index is performed.

[0028] For example, if a waybill whose license plate number or mobile phone number contains the character "31" needs to be found, the data in the summary field is concatenated from the license plate "YueBxxxx3" and the mobile phone number of the consignor "132456yyyyy". If no space is added, the summary field value is "YueBxxxx3132456yyyyy". If a query is performed, this data will be found, but in fact it does not meet the conditions. If a space is added, the summary field value becomes "YueBxxxx3 132456yyyyy", so it will not be found.

[0029] Further, S205 specifically includes the following operation steps:

[0030] S2051, the update data scheduling module consumes the update data of the business table cached by the Kafka cache service;

[0031] S2052, the scheduling program hands over the update data to the corresponding business processing logic for data processing.

[0032] Further, the service processing logic in the S206 is associated with the data of the current service table corresponding to the main Key, the data of the field corresponding to the current table in the service physical view is obtained, and the data is processed according to the query condition.

[0033] Further, the data processing processing mode in the S206 includes:

[0034] S2061, the value of the service field corresponding to the main Key is extracted as the target data, and no further processing is required;

[0035] S2062, the value of the service field corresponding to the main Key is extracted, and the target data is obtained by operation according to the context of the service logic;

[0036] S2063, the values of the fields of multiple service tables corresponding to the main Key are extracted and spliced, the splicing mode is according to the splicing formula, and the target data is obtained;

[0037] The service logic processing is performed on the data to generate the data that can be directly queried before being stored in the service physical view table, like the above summary field value processing logic, the multiple field values are spliced together through the data splicing mode, and one field can be used for query, without multiple fields for separate query, which greatly improves the query speed.

[0038] Compared with the prior art, the present application has the following advantages:

[0039] The present application establishes a service physical view service, creates and updates the service physical view to be queried in real time, and performs full-text indexing on the service physical view, to provide single physical view fast fuzzy query for service query, so that the fuzzy query is not limited by the left and right fuzzy queries, the query efficiency is reduced from tens of seconds to tens of milliseconds, the query efficiency is greatly improved, the user experience is greatly improved, and the query advantage is more embodied for the multi-table joint query and multi-field simultaneous query. BRIEF DESCRIPTION OF DRAWINGS

[0040] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings needed to be used in the embodiments or the prior art description. Obviously, the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can also be obtained without creative labor.

[0041] Fig. 1 The working principle diagram of the method for improving data retrieval speed in an embodiment of the present application;

[0042] Fig. 2A partial structure diagram of physical view processing logic in an embodiment of the present application. DETAILED DESCRIPTION

[0043] The present application will be described in detail below with reference to the embodiments shown in the drawings. However, the embodiments do not limit the present application, and the changes in structure, method or function made by those skilled in the art based on the embodiments are included in the protection scope of the present application.

[0044] The present application discloses a method for improving data retrieval speed, referring to Figs. 1-2 as shown, comprising the following steps:

[0045] S1, generating a multi-table fuzzy query request;

[0046] S2, building a business physical view service;

[0047] S3, dimension reduction of the multi-table fuzzy query request, single table query of the business physical view;

[0048] The business physical view is classified as a single physical view for the same type of business function, and the data stored in the business physical view is subjected to business logic processing before storage to obtain data available for direct query.

[0049] By combining the probe service with the business physical view, the multi-table joint and multi-field fuzzy query are dimensionally reduced to single table field query of the business physical view, and the query is concentrated in the business physical view table, which can effectively avoid the defects of index invalidation caused by multi-table joint and multi-field fuzzy matching of the relational database.

[0050] For example, in the TMS system, the waybill list is found by various conditions (waybill number, carrier mobile phone number, license plate number, loading and unloading location, etc.), which are classified into a category, and the corresponding waybill number list is found, only the query conditions are different, so a physical view table is established with the waybill number as the main Key and the corresponding fields of the query conditions as other fields, and the business logic only queries the business physical view table to obtain the required data.

[0051] The business physical view service in S2 includes a probe service, a Kafka cache service and a physical view processing logic, the probe service is used as a slave database of a MySQL database, real-time monitoring of the changes of the master database, and the collected change data is sent to the Kafka service for caching, the probe service can well solve the synchronization of business physical view data and business table data, avoid the problem that the business table data changes, but the business physical view table data is not updated in time, and does not need to modify the logic of the original business system, so that the business system and the business physical view service are decoupled, facilitating development and expansion.

[0052] In addition, the Kafka cache service is used to store the business table change data obtained by the probe service, and the high throughput and high cache of the Kafka cache service can ensure that the data is not lost, and if the physical view processing logic service as a consumer end is restarted, the cached data will not be lost, and after the restart, the consumption can continue from the consumption point before the restart, ensuring that the change data of the business table can be provided to the physical view processing logic for processing.

[0053] Reference Figs. 1-2 As shown, the physical view processing logic is the core of the entire service, and the physical view processing logic includes the following operation steps:

[0054] S201, classifying the business query result, and establishing a business physical view table for each type of query result to determine the type of business physical view table to be established;

[0055] For example, if the TMS system needs to provide a fuzzy query function for the two functions of the cargo list and the waybill list, and if the waybill list needs to be queried for the waybill number, the carrier's mobile phone number, the license plate number, and the cargo name, regardless of which condition is queried, the commonality will return the waybill list, so a business physical view table with the waybill number as the primary key is established. If the cargo list needs to be queried for the detailed address, the consignee's name, and the cargo name, a business physical view table with the cargo number as the primary key needs to be established, and so on.

[0056] S202, after business classification, determine the fields corresponding to each business physical view table;

[0057] S203, if the fuzzy query result contains multiple fields, add a summary field to the business physical view table and store the summary values of the multiple fields;

[0058] S204, respectively establish full-text indexes for the fields and summary fields of the business physical view;

[0059] S205, update the business physical view data in real time;

[0060] S206, the business processing logic processes the data;

[0061] S207, the processed data is saved to the business physical view table through the business physical view update module.

[0062] Among them, the business physical view table in S202 is established on the basis of multiple business data tables, and is a subset of data items of each business table, which is abstracted from the fields involved in the filtering conditions.

[0063] In addition, the design principle of the service physical view table in S202 is as follows:

[0064] S2021, the same kind of service determines a main Key, and establishes a corresponding service physical view table;

[0065] S2022, the field corresponding to each filter condition of the same kind of service is extracted from the corresponding service table as the field of the service physical view table.

[0066] Specifically, the MySQL in S204 supports 2-character field-level full-text indexing by default, and requires at least 2 characters to be entered in the user query interface to perform a query. If single-character fuzzy queries are required, the configuration in MySQL needs to be modified to support single-character fuzzy queries.

[0067] Because the summary field is data generated by merging multiple field data, in order to use full-text indexing on the summary field, special processing must be performed on the composition of the summary field content, that is, the summary field value in S204 = field1 value + space + field2 value + space + … + space + fieldN value. Spaces are added between each field value to prevent the query result value from being split into two fields when performing summary field full-text indexing.

[0068] For example, if you need to find a shipment whose license plate number or mobile phone number contains the "31" character, the data in the summary field is concatenated from the license plate "YueBxxxx3" and the mobile phone number of the consignor "132456yyyyy". If no space is added, the summary field value is "YueBxxxx3132456yyyyy". If a query is performed, this data will be found, but in fact it does not meet the conditions. If a space is added, the summary field value becomes "YueBxxxx3132456yyyyy", so it will not be queried out.

[0069] In addition, S205 specifically includes the following operation steps:

[0070] S2051, the update data scheduling module consumes the update data of the service table cached by the Kafka cache service;

[0071] S2052, the scheduling program hands over the update data to the corresponding service processing logic for data processing.

[0072] Furthermore, the service processing logic in S206 is associated with the data of the service table corresponding to the main Key, obtains the data of the field corresponding to the table in the service physical view, and then processes the data according to the query conditions.

[0073] Further, the data processing and processing mode in S206 includes:

[0074] S2061. Extracting the value of the business field corresponding to the main key is the target data, and no further processing is required.

[0075] S2062. Extracting the value of the business field corresponding to the main key requires calculation based on the context of the business logic to obtain the target data;

[0076] S2063. Extract the field values ​​of the main key corresponding to the multiple business tables, summarize and concatenate them, and obtain the target data by following the concatenation formula.

[0077] The purpose of processing data with business logic is to generate data that can be directly queried before it is stored in the business physical view table. For example, according to the summary field value processing logic above, multiple field values ​​are combined by data concatenation, and can be queried by a single field instead of querying multiple fields separately, which greatly improves query speed.

[0078] A method to improve data retrieval speed includes the following steps:

[0079] Step 1: Analyze the business operations that require single / multiple table queries, categorize and summarize them, organize the business physical view tables that need to be established and related fields, and determine the primary key for each type of business physical view table;

[0080] Step 2: Create a business physical view table in MySQL, and set a full-text index with an ngram parser for the fields that need to be fuzzy searched and the summary fields. You can limit the minimum number of characters in the query input according to business requirements, or modify the full-text index configuration of MySQL accordingly to adapt to business needs.

[0081] Step 3: Set up a probe service and run it as a MySQL slave database. It can monitor the corresponding fields of the business tables that need to be monitored, including monitoring database operations such as insertion, deletion, and update.

[0082] Step 4: Configure the connection between the probe service and Kafka. When the probe service detects data changes in the business table, it will quickly push the changed data to Kafka.

[0083] Step 5: Update the data scheduling logic by consuming updated data from Kafka and, based on the data table information, hand it over to the corresponding business processing logic for processing.

[0084] Step 6: The table business processing logic is responsible for extracting the values ​​of the corresponding fields in this data table based on the main key, and processing the data in accordance with the business logic requirements of the query conditions. This includes summarizing and splicing data to form data that can be directly queried, and submitting it to the business physical view update module.

[0085] Step 7, the business physical view update module stores the processed data into the physical view table, and since full-text indexes are set for the fields and the summary fields, MySQL automatically indexes the content;

[0086] Step 8, when the business system performs a multi-table fuzzy query, the original fuzzy query mode using LIKE "% xxx %" needs to be changed to the mode of SELECT * FROM business physical view table WHERE MATCH (physical view field) AGAINST ("query content" IN BOOLEAN MODE) for query.

[0087] The application combines the probe service with the business physical view, fully utilizes the advantages of the physical view and the MySQL full-text index, and supports the business system to perform multi-table joint multi-field query; the probe service can make the data of the business physical view be updated in real time, the final result data is stored in the physical view mode through the business processing logic, so that the business system does not need to involve the joint operation of multiple tables when querying, and only needs to perform single-table operation of the physical view table; the full-text index of the fields and the summary fields to be queried can effectively utilize the data index function of MySQL, prevent the index invalidation caused by the LIKE left and right fuzzy query from causing the disadvantage of full table scanning, maximally improve the system performance and efficiency, and greatly improve the user query experience. The application can also be used in other scenes of fuzzy query.

[0088] For those skilled in the art, it is obvious that the application is not limited to the details of the above exemplary embodiments, and the application can be implemented in other specific forms without departing from the spirit or essential characteristics of the application. Therefore, the embodiments should be regarded as exemplary and non-limiting, the scope of the application is defined by the appended claims rather than the above description, and it is intended to include all changes falling within the meaning and scope of the equivalent elements of the claims. Any reference signs in the claims should not be regarded as limiting the claims involved.

[0089] In addition, it should be understood that although the present specification is described in terms of embodiments, not every embodiment contains only one independent technical solution, and the description manner of the specification is only for the sake of clarity, and those skilled in the art should regard the specification as a whole, and the technical solutions in the embodiments can also be properly combined to form other embodiments that those skilled in the art can understand.

Claims

1. A method for improving data retrieval speed, characterized by, The method comprises the following steps: S1, generating a multi-table fuzzy query request; S2, building a business physical view service; S3, dimension reduction multi-table fuzzy query request, single table query of business physical view; Wherein, the business physical view is classified as a single physical view for the same type of business function, and the data stored in the business physical view is subjected to business logic processing before storage to obtain data available for direct query, the business physical view service in S2 includes probe service, Kafka cache service and physical view processing logic, the physical view processing logic includes the following operation steps: S201, classifying business query results, and establishing a business physical view table for each type of query result to determine the type of business physical view table; S202, after business classification, determine the fields corresponding to each business physical view table; S203, if the fuzzy query contains the results of multiple fields, add a summary field to the business physical view table, and store the summary values of multiple fields; S204, respectively establish full-text indexes for the fields and summary fields of the business physical view; S205, real-time update of business physical view data; S206, data processing by business processing logic; S207, the processed data is saved to the business physical view table through the business physical view update module.

2. The method of claim 1, wherein, The probe service is used as a slave database of a MySQL database, which monitors the changes of the master database in real time, and sends the collected change data to the Kafka service for caching, and the Kafka cache service is used to store the business table change data obtained through the probe service.

3. The method of claim 2, wherein, The business physical view table in S202 is established on the basis of multiple business data tables, which is a subset of data items of each business table extracted from the fields involved in the filtering conditions.

4. The method of claim 3, wherein, The design principles of the business physical view table in S202 are as follows: S2021, determine a main key for the same type of business, and establish a corresponding business physical view table; S2022, the fields corresponding to each filtering condition of the same type of business are extracted from the corresponding business table as the fields of the business physical view table.

5. The method of claim 2, wherein the step of searching the database comprises the step of: In S204, MySQL supports 2-character field-level full-text indexing by default, and requires at least 2 characters to be entered in the user query interface to perform a query, if single-character fuzzy query is required, the configuration in MySQL needs to be modified to support single-character fuzzy query. ​ 6. The method of claim 2, wherein the step of searching the database comprises the step of: In S204, the summary field value = field1 value + space + field2 value + space + … + space + fieldN value, and spaces are added between each field value to prevent the combination of the values of the two fields before and after the query result value from belonging to two fields. ​ 7. The method of claim 2, wherein the step of generating the index comprises the step of: S205 specifically includes the following operation steps: ​ S2051, the update data scheduling module consumes the update data of the business table cached by the Kafka cache service; S2052, the scheduling program hands over the update data to the corresponding business processing logic for data processing.

8. The method of claim 4, wherein the step of searching the database comprises the step of: The service processing logic in the S206 is associated with the data of the service table corresponding to the main Key, the data of the field corresponding to the table in the service physical view is obtained, and the data is processed according to the query condition. ​ 9. The method of claim 8, wherein, The data processing processing mode in the S206 includes: S2061, the value of the service field corresponding to the main Key is the target data, and no further processing is required; S2062, the value of the service field corresponding to the main Key is extracted, and the target data is obtained according to the operation of the context of the service logic; S2063, the values of the fields of multiple service tables corresponding to the main Key are extracted and spliced, and the target data is obtained according to the splicing formula.

Images