CN116186073A - A Method of Improving Data Retrieval Speed - Google Patents

Abstract

The invention belongs to the technical field of data retrieval, and specifically relates to a method for increasing data retrieval speed, comprising the following steps: S1, generating a multi-table fuzzy query request; S2, building a business physical view service; S3, reducing dimensionality and multi-table fuzzy query requests, Perform single-table query on the business physical view; among them, the business physical view is to classify the same type of business function as a single physical view. The present invention 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, and provides fast fuzzy query of a single physical view for business query, so that the fuzzy query is not subject to left and right fuzzy query The query efficiency drops from tens of seconds to hundreds of seconds to tens of milliseconds, which greatly improves the query efficiency and greatly improves the user experience, especially for multi-table joint query, multi-field simultaneous query reflects the query The advantages.

Description

A Method of Improving Data Retrieval Speed

technical field

The invention belongs to the technical field of data retrieval, and in particular relates to a method for improving data retrieval speed.

Background technique

During the development of TMS systems or CRM systems using MySQL relational databases, customers often need to perform fuzzy queries on certain information. For example, there is a query input box on the waybill list interface of the applet. For all the waybills corresponding to the waybill number or mobile phone number or license plate number or address of the "21" character, this query usually involves multiple business tables: the waybill table containing the waybill number, the account table containing the mobile phone number, including Data tables such as the waybill capacity table of the license plate number and the supply table containing address information require multiple tables to be combined to perform multi-field fuzzy queries. When the amount of data in various tables is relatively large, it will be very slow to perform such a query. The interface often times out due to the long data query time, resulting in the function not being used normally. At the same time, querying under high concurrency will lead to a sharp drop in database performance.

In order to avoid the above-mentioned slow query situation, most of the current methods use the characteristics of MySQL. In order to ensure that the index can be used to prohibit left fuzzy query during development, only right fuzzy query can be performed; or use search engine systems, such as ES for full text search. However, these two methods have disadvantages. Prohibition of left fuzzy query will lead to the inability to know the beginning character in the actual scene and make the query impossible; and the full-text search of ES is not suitable for situations where not all field data in TMS or CRM systems are plain text .

Therefore, in view of the above technical problems, it is necessary to provide a method for improving the speed of data retrieval.

Contents of the invention

The purpose of the present invention is to provide a method for improving data retrieval speed, so as to solve the above-mentioned problems of slow data speed and low efficiency of multi-table joint fuzzy query.

In order to achieve the above object, the technical solution provided by an embodiment of the present invention is as follows:

A method of increasing data retrieval speed, comprising the steps of:

S1. Generate a multi-table fuzzy query request;

S2. Building a business physical view service;

S3, Dimensionality reduction and multi-table fuzzy query request, perform single-table query on the business physical view;

Among them, the business physical view classifies the same type of business functions into a single physical view, and the data stored in the business physical view is to perform business logic processing on the business table data before storage to obtain data that can be directly queried.

Further, the business physical view service in S2 includes probe service, Kafka cache service and physical view processing logic, and the probe service is used as a slave library of a MySQL database to monitor changes in master database data in real time, and Send the collected change data to the Kafka service for caching. The probe service can well solve the problem of synchronizing the business physical view data with the business table data, and avoid the business table data changing, but the business physical view table data is not updated in time At the same time, there is no need to modify the logic of the original business system, so that the business system and the business physical view service can be decoupled to facilitate development and expansion;

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

Further, the physical view processing logic includes the following steps:

S201. Classify the business query results, and establish a business physical view table for each type of query results, to determine the type of business physical view table to be established;

S202. After the business classification, determine the fields corresponding to each business physical view table;

S203. If the fuzzy query contains the results of multiple fields at the same time, add a summary field to the business physical view table, and store the summary values of the multiple fields;

S204. Establish full-text indexes for the fields and summary fields of the business physical view;

S205. Update the business physical view data in real time;

S206. The business processing logic performs data processing;

S207. The processed data is saved in the business physical view table through the business physical view update module.

Further, the business physical view table in S202 is a business physical view table based on multiple business data tables, which is extracted from the fields involved in each business table according to the filter conditions, and is the data item of each business table Subset.

Further, the design principle of the service physical view table in S202 is as follows:

S2021. Determine a master key for similar services, and create a corresponding service physical view table;

S2022. For the fields corresponding to the filter conditions of the same kind of business, extract the corresponding fields from the corresponding business table as the fields of the business physical view table.

Further, in the above-mentioned S204, MySQL supports a field-level full-text index of 2 characters by default, and it is required to input at least 2 characters in the user query interface to perform a query. If fuzzy query of a single character needs to be supported, the configuration in MySQL needs to be modified to Support single word fuzzy query.

Further, because the summary field is data generated by merging multiple field data, in order to use the full-text index for the summary field, special processing must be done to 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, add a space between each field value to prevent the two field values before and after the summary field from being put together to cause the query result value to belong to 2 fields;

For example: you need to find the waybill whose license plate number or mobile phone number contains the character "31". The data in the summary field is composed of the license plate "粤Bxxxx3" and the carrier's mobile phone number "132456yyyyy". If no spaces are added, the summary field value is "粤Bxxxx3132456yyyyy", if the query is performed, the data will be found out, but in fact it is not eligible data, if a space is added, the summary field value will become "Guangdong Bxxxx3 132456yyyyy", so it will not be queried.

Further, the S205 specifically includes the following steps:

S2051. Update the data scheduling module to consume the update data of the service table cached by the Kafka cache service;

S2052. The scheduler transfers the updated data to the corresponding business processing logic for data processing.

Further, the business processing logic in S206 is to associate according to the data of the business table corresponding to the main key, obtain the data of the corresponding fields of the table in the business physical view, and then process the data according to the query conditions.

Further, the data processing mode in S206 includes:

S2061, extracting the value of the business field corresponding to the main Key is the target data, without further processing;

S2062, extracting the value of the business field corresponding to the main Key needs to be calculated according to the context of the business logic to obtain the target data;

S2063, extracting the field values corresponding to the main Key to perform summary splicing, and the splicing mode is according to the splicing formula to obtain the target data;

The business logic processing of data is to generate data that can be directly queried before it is stored in the business physical view table. For example, according to the above summary field value processing logic, multiple field values are stitched together through data splicing. One field can be queried, instead of multiple fields to be queried separately, which greatly improves the query speed.

Compared with the prior art, the present invention has the following advantages:

The present invention 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, and provides fast fuzzy query of a single physical view for business query, so that fuzzy query is not affected by left and right fuzzy query The query efficiency drops from tens of seconds to hundreds of seconds to tens of milliseconds, which greatly improves the query efficiency and greatly improves the user experience, especially for multi-table joint query, multi-field simultaneous query reflects the query The advantages.

Description of drawings

In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings that need to be used in the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are only These are some embodiments described in the present invention. Those skilled in the art can also obtain other drawings based on these drawings without creative work.

Fig. 1 is a working principle diagram of a method for improving data retrieval speed in an embodiment of the present invention;

FIG. 2 is a schematic diagram of a partial structure of physical view processing logic in an embodiment of the present invention.

Detailed ways

The present invention will be described in detail below in conjunction with various embodiments shown in the drawings. However, these embodiments do not limit the present invention, and any structural, method or functional changes made by those skilled in the art according to these embodiments are included in the protection scope of the present invention.

The invention discloses a method for improving data retrieval speed, as shown in Fig. 1-Fig. 2, comprising the following steps:

S1. Generate a multi-table fuzzy query request;

S2. Building a business physical view service;

S3, Dimensionality reduction and multi-table fuzzy query request, perform single-table query on the business physical view;

Among them, the business physical view classifies the same type of business functions into a single physical view, and the data stored in the business physical view is to perform business logic processing on the business table data before storage to obtain data that can be directly queried.

By combining the probe service with the business physical view, the multi-table union and multi-field fuzzy query are reduced to the 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 multi-table join of the relational database. Table, the defect that the fuzzy matching of multiple fields causes the index to fail.

For example, in the TMS system, through various conditions (waybill number, carrier’s mobile phone number, license plate number, loading and unloading location, etc.) to fuzzily search the list of waybills, and classify them into one category, it is necessary to find the list of corresponding waybill numbers. However, the query conditions are different, so a physical view table with the waybill number as the main key and other fields composed of fields corresponding to the query conditions is established. The business logic can only query the business physical view table to obtain the required data.

Wherein, the business physical view service in S2 includes a probe service, a Kafka cache service, and physical view processing logic, and the probe service is used as a slave database of a MySQL database to monitor changes in master database data in real time, and The collected change data is sent to the Kafka service for caching. The probe service can well solve the problem of synchronizing the business physical view data with the business table data, avoiding the problem that the business table data changes, but the business physical view table data is not updated in time At the same time, there is no need to modify the logic of the original business system, so that the business system and the business physical view service are decoupled, which is convenient for development and expansion.

In addition, 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 data will not be lost. Restarting will not lose the cached data. After restarting, you can continue to consume from the consumption point before restarting, ensuring that the changed data of the business table can be completely provided to the physical view processing logic for processing.

Referring to Figures 1-2, the physical view processing logic is the core of the entire service, and the physical view processing logic includes the following steps:

S201. Classify the business query results, and establish a business physical view table for each type of query results, to determine the type of business physical view table to be established;

For example, if the TMS system needs to provide a fuzzy query function for the two functions of the source list and the waybill list, and for the waybill list, if it needs to perform fuzzy query on the waybill number, carrier mobile phone number, license plate number, cargo name, etc., no matter which condition is used for query, The commonality will return the waybill number list, so create a business physical view table with the waybill number as the main key. If you need to fuzzy query the detailed address, consignee name, and cargo name in the source list, you need to create a source of goods The number is used as the business physical view table of the main key, and so on.

S202. After the business classification, determine the fields corresponding to each business physical view table;

S203. If the fuzzy query contains the results of multiple fields at the same time, add a summary field to the business physical view table, and store the summary values of the multiple fields;

S204. Establish full-text indexes for the fields and summary fields of the business physical view;

S205. Update the business physical view data in real time;

S206. The business processing logic performs data processing;

S207. The processed data is saved in the business physical view table through the business physical view update module.

Wherein, the business physical view table in S202 is a business physical view table established on the basis of multiple business data tables, which is extracted from the fields involved in each business table according to the filter conditions, and is the data item of each business table. set.

In addition, the design principle of the business physical view table in S202 is as follows:

S2021. Determine a master key for similar services, and create a corresponding service physical view table;

S2022. For the fields corresponding to the filter conditions of the same kind of business, extract the corresponding fields from the corresponding business table as the fields of the business physical view table.

Specifically, in the above-mentioned S204, MySQL supports field-level full-text indexing of 2 characters by default, and it is required to input at least 2 characters in the user query interface to perform a query. If fuzzy query of a single character needs to be supported, the configuration in MySQL needs to be modified to Support single word fuzzy query.

Because the summary field is the data generated by merging multiple field data, in order to use the full-text index for the summary field, special processing must be done to 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, add a space between each field value to prevent the two field values before and after the summary field from being put together to cause the query result value to belong to two fields.

For example: you need to find the waybill whose license plate number or mobile phone number contains the character "31". The data in the summary field is composed of the license plate "粤Bxxxx3" and the carrier's mobile phone number "132456yyyyy". If no spaces are added, the summary field value is "粤Bxxxx3132456yyyyy", if the query is performed, the data will be found out, but in fact it is not eligible data, if a space is added, the summary field value will become "Guangdong Bxxxx3132456yyyyy", so it will not be queried.

In addition, the S205 specifically includes the following steps:

S2051. Update the data scheduling module to consume the update data of the service table cached by the Kafka cache service;

S2052. The scheduler transfers the updated data to the corresponding business processing logic for data processing.

Furthermore, the business processing logic in S206 is to associate according to the data of the business table corresponding to the main Key, obtain the data of the corresponding field of the table in the business physical view, and then perform business logic processing on the data according to the query conditions.

Further, the data processing mode in S206 includes:

S2061, extracting the value of the business field corresponding to the main Key is the target data, without further processing;

S2062, extracting the value of the business field corresponding to the main Key needs to be calculated according to the context of the business logic to obtain the target data;

S2063, extracting the field values corresponding to the main Key to perform summary splicing, and the splicing mode is according to the splicing formula to obtain the target data.

The business logic processing of data is to generate data that can be directly queried before it is stored in the business physical view table. For example, according to the above summary field value processing logic, multiple field values are stitched together through data splicing. One field can be queried, instead of multiple fields to be queried separately, which greatly improves the query speed.

A method for improving data retrieval speed, specifically comprising the following steps:

Step 1. Analyze the business that requires single/multi-table query, classify and summarize, sort out the business physical view table and related fields that need to be established, and determine the primary key of each type of business physical view table;

Step 2. Create a business physical view table in MySQL, and set a full-text index with an ngram parser for the fields that require fuzzy query and summary fields. You can limit the minimum number of words entered in the query according to business requirements, and you can also modify MySQL accordingly Full-text index configuration to meet business needs;

Step 3. Build the probe service and run it as MySQL slave database. It can monitor the corresponding fields of the business tables that need to be monitored, including monitoring the operations of inserting, deleting, updating and other databases;

Step 4. Configure the connection between the probe service and Kafka. When the probe service monitors data changes in the business table, it will quickly push the changed data to Kafka;

Step 5. Update the data scheduling logic to consume the updated data from Kafka, and hand it over to the business processing logic corresponding to the table for processing according to the data table information;

Step 6. The table business processing logic is responsible for extracting the value of the corresponding field of the data table associated with the primary key, and combining the business logic requirements of the query conditions for data processing, including summary and splicing data processing, to form data for direct query , submitted to the business physical view update module;

Step 7. The business physical view update module stores the above-mentioned processed data in the physical view table. Since the full-text index is set for the field and summary field, MySQL automatically performs word segmentation index for the content;

Step 8. When the business system performs multi-table fuzzy query, it needs to change the original fuzzy query method of using LIKE "%xxx%" to use SELECT*FROM business physical view table WHERE MATCH (physical view field) AGAINST(" query content" IN BOOLEAN MODE) mode to query.

The present invention combines the probe service with the business physical view, fully utilizes the advantages of the physical view and MySQL full-text index, and supports the business system to perform multi-table joint multi-field query; the data of the business physical view can be updated in real time by using the probe service , 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, but only needs to perform single-table operations on the physical view table; the fields that need to be queried and the summary fields Full-text indexing can effectively utilize the data indexing function of MySQL, prevent the disadvantages of full-table scanning caused by fuzzy queries caused by LIKE left and right, improve system performance and efficiency to the greatest extent, and greatly improve user query experience. Other scenarios that can be used for fuzzy queries.

It will be apparent to those skilled in the art that the invention is not limited to the details of the above-described exemplary embodiments, but that the invention can be embodied in other specific forms without departing from the spirit or essential characteristics of the invention. Accordingly, the embodiments should be regarded in all points of view as exemplary and not restrictive, the scope of the invention being defined by the appended claims rather than the foregoing description, and it is therefore intended that the scope of the invention be defined by the appended claims rather than by the foregoing description. All changes within the meaning and range of equivalents of the elements are embraced in the present invention. Any reference sign in a claim should not be construed as limiting the claim concerned.

In addition, it should be understood that although the specification is described according to the embodiments, not each embodiment only includes an independent technical solution, and this description in the specification is only for clarity, and those skilled in the art should take the specification as a whole , the technical solutions in the various embodiments can also be properly combined to form other implementations that can be understood by those skilled in the art.

Claims (10)

1. A method for improving data retrieval speed, comprising the following steps: S1. Generate a multi-table fuzzy query request; S2. Building a business physical view service; S3, Dimensionality reduction and multi-table fuzzy query request, perform single-table query on the business physical view; Among them, the business physical view classifies the same type of business functions into a single physical view, and the data stored in the business physical view is to perform business logic processing on the business table data before storage to obtain data that can be directly queried. 2. A kind of method for improving data retrieval speed according to claim 1, it is characterized in that, the business physical view service among described S2 comprises probe service, Kafka cache service and physical view processing logic, and described probe service It is used as a slave library of a MySQL database to monitor the changes of the main database data in real time, and send the collected change data to the Kafka service for caching. The Kafka cache service is used to store the data obtained through the probe service. Business table change data. 3. A method for improving data retrieval speed according to claim 2, wherein the physical view processing logic comprises the following steps: S201. Classify the business query results, and establish a business physical view table for each type of query results, to determine the type of business physical view table to be established; S202. After the business classification, determine the fields corresponding to each business physical view table; S203. If the fuzzy query contains the results of multiple fields at the same time, add a summary field to the business physical view table, and store the summary values of the multiple fields; S204. Establish full-text indexes for the fields and summary fields of the business physical view; S205, updating the business physical view data in real time; S206. The business processing logic performs data processing; S207. The processed data is saved in the business physical view table through the business physical view update module. 4. A method for improving data retrieval speed according to claim 3, characterized in that, the business physical view table in S202 is to set up a business physical view table on the basis of multiple business data tables, and is obtained from each business The table is formed by extracting the fields involved in the filter conditions, and is a subset of data items of each business table. 5. a kind of method for improving data retrieval speed according to claim 3, is characterized in that, the design principle of business physical view table among the described S202 is as follows: S2021. Determine a master key for similar services, and create a corresponding service physical view table; S2022. For the fields corresponding to the filter conditions of the same kind of business, extract the corresponding fields from the corresponding business table as the fields of the business physical view table. 6. A kind of method for improving data retrieval speed according to claim 3, it is characterized in that, among the described S204, MySQL supports the field-level full-text index of 2 words by default, requires to input at least 2 words in user query interface and just can carry out Query, if you need to support single-word fuzzy query, you need to modify the configuration in MySQL to support single-word fuzzy query. 7. A kind of method for improving data retrieval speed according to claim 3, it is characterized in that, summary field value=field 1 value+space+field 2 value+space+…+space+field N value among the described S204, Add spaces between each field value to prevent the query result value from belonging to two fields when the two field values are joined together when the full-text index of the summary field is performed. 8. A method for increasing data retrieval speed according to claim 3, wherein said S205 specifically comprises the following steps: S2051. Update the data scheduling module to consume the update data of the service table cached by the Kafka cache service; S2052. The scheduler transfers the updated data to the corresponding business processing logic for data processing. 9. A method for improving data retrieval speed according to claim 3, characterized in that, the business processing logic in S206 is to associate according to the data in the business table corresponding to the main Key, and obtain the data in the business physical view. The data in the corresponding fields of this table is processed according to the business logic of the query conditions. 10. A method for improving data retrieval speed according to claim 9, characterized in that, the data processing mode in S206 includes: S2061, extracting the value of the business field corresponding to the main Key is the target data, without further processing; S2062, extracting the value of the business field corresponding to the main Key needs to be calculated according to the context of the business logic to obtain the target data; S2063, extracting the field values corresponding to the main Key to perform summary splicing, and the splicing mode is according to the splicing formula to obtain the target data.

Images