Optimization method and system for high-frequency data query

Abstract

The invention relates to the technical field of database query optimization, in particular to a high-frequency data query optimization method and system.The method comprises the steps that a function module for querying data statistics is newly added to the bottom layer of a database, and the function module is used for recording the frequency of using a certain piece of data within unit time; the frequency of the data is recorded in a cache layer of a database; an asynchronous thread is created, the asynchronous thread is used for migrating a plurality of pieces of data to a new page, and the new page is generated; when the upper business layer needs to obtain data, the cache layer of the database is queried preferentially according to the id, data meeting the query condition in the newly generated page can be directly queried, and at the moment, the newly generated page is returned; according to the method, the high-frequency data is concentrated into a few Page pages, other high-frequency data are loaded into the cache by querying one high-frequency data, and the subsequent high-frequency query can be hit in the cache, so that the query efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database query optimization, and in particular to an optimization method and system for high-frequency data query. Background Art

[0002] The Page in the database is the basic unit of data storage and the smallest I / O unit in the database. Data is read and written in pages.

[0003] To improve query efficiency, most current databases use a database cache layer to temporarily cache data. Typically, at the lowest level of a database, this cache layer is a page, which can contain multiple pieces of data. Each time data is retrieved, the cache is first queried based on the ID. If the data exists, a page of data is directly returned. This means that to maximize resource utilization, even if a query is for a single piece of data, an entire page of data is returned, containing multiple other pieces of data. If the current database has a large amount of data, frequently accessed data items are physically dispersed across different pages, with a single page containing other unrelated data. Using traditional methods to query high-frequency data can lead to inefficient queries. Summary of the Invention

[0004] In order to overcome the problem in the prior art that when querying a database with a large amount of data, a high-frequency data item can only be loaded on the Page where it is located, and the Page contains other irrelevant data, resulting in low query hit rate and low query efficiency, the purpose of the present invention is to provide an optimization method and system for high-frequency data query, which can concentrate the high-frequency data scattered in various places into a few Page pages, so that a single cache load can cover multiple high-frequency data. When querying one high-frequency data, the others are loaded into the cache, and subsequent queries on these high-frequency data can hit the cache, thereby improving query efficiency.

[0005] The present invention is implemented by the following scheme:

[0006] A method for optimizing high-frequency data query, the method steps are as follows:

[0007] Step 1: Add a new function module for querying data statistics in the bottom layer of the database. The function module is used to record the frequency of all data being used within a unit time and record the frequency of the data in the cache layer of the database;

[0008] Step 2: Create an asynchronous thread to migrate several pieces of data from step 1 to a new page, generating a new page.

[0009] Step 3: When the upper business layer needs to obtain data, it queries according to the ID, and first queries the cache layer of the database. It can directly query the data that meets the query conditions in the newly generated page, and then returns the newly generated page;

[0010] Step 4: Cache the page obtained by the upper business layer for subsequent queries.

[0011] Furthermore, the functional module in step 1 records data in a manner of sorting in reverse order of frequency.

[0012] Furthermore, after the page is cached in step 4, the expiration time of the page data in the cache can be customized.

[0013] A high-frequency data query optimization system, comprising: a data statistics module, a data migration module, a page query module, and a data cache module;

[0014] The data statistics module is used to add a new query data statistics function unit in the bottom layer of the database. The function unit is used to record the frequency of use of a certain data in a page within a unit time. The frequency of the data is also recorded in the cache layer of the database.

[0015] The data migration module is used to create an asynchronous thread, which is used to migrate data from several data statistics modules to a new page to generate a new page;

[0016] The page query module is used to query the cache layer of the database first when the upper business layer needs to obtain data based on the ID query, and can directly query the data that meets the query conditions in the newly generated page, and then return the newly generated page;

[0017] The data cache module is used to cache the pages obtained by the upper business layer for subsequent query use.

[0018] Furthermore, the functional modules in the data statistics module record data in a manner of sorting in reverse order of frequency.

[0019] Furthermore, after a page is cached in the data cache module, the expiration time of the page data in the cache can be customized.

[0020] The beneficial effects of the present invention are:

[0021] The present invention provides an optimization method and system for high-frequency data queries. Each time a data query is performed, statistics are compiled on the data queried over a period of time. For high-volume data queries, data on a page is migrated and a new page is generated to provide faster access. Because the page is already stored in the database's cache layer, migrating and reorganizing the data on the page does not significantly increase resource consumption. However, for higher-level data queries, the ability to query a single page for other data that may be needed later greatly improves overall query efficiency and enhances system performance.

[0022] By centralizing the high-frequency data scattered throughout into a few Page pages, a single cache load can cover multiple high-frequency data items. Querying one high-frequency data item is likely to load several other high-frequency data items into the cache as well. Subsequent queries for these high-frequency data items can all be hit in the cache, significantly reducing access to the underlying storage. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 is a flow chart of the method of the present invention;

[0024] Figure 2 It is a structural block diagram of the system of the present invention. DETAILED DESCRIPTION

[0025] The present invention will be further described below with reference to the accompanying drawings.

[0026] See also Figure 1 , an optimization method for high-frequency data query, the method steps are as follows:

[0027] Step 1: Add a new function module for querying data statistics in the bottom layer of the database. The function module is used to record the frequency of use of a certain data within a unit time and record the frequency of the data in the cache layer of the database;

[0028] Step 2: Create an asynchronous thread to migrate several pieces of data from step 1 to a new page, generating a new page.

[0029] Step 3: When the upper business layer needs to obtain data, it queries according to the ID, and first queries the cache layer of the database. It can directly query the data that meets the query conditions in the newly generated page, and then returns the newly generated page;

[0030] Step 4: Cache the page obtained by the upper business layer for subsequent queries.

[0031] The present invention will be further described below with reference to a specific embodiment:

[0032] A method for optimizing high-frequency data queries, comprising the following steps:

[0033] Step 1: Add a new function module for querying data statistics in the bottom layer of the database. The function module is used to record the frequency of use of a certain data within a unit of time, and record the frequency of the data in the cache layer of the database, so that there will not be much additional consumption in terms of efficiency; for the results of the statistics in step 1, the data is recorded in reverse order of frequency.

[0034] Step 2: Create an asynchronous thread to migrate several pieces of data from step 1 to a new page, generating a new page.

[0035] Assuming a page stores 10 data items (this can be configured. For example, the default page in MySQL can store 16KB of data, with a fixed number of items), we retrieve the 10 most frequently used data items and migrate them to a new page. Similarly, we generate new pages for subsequent sorted data according to this rule, until a page containing at most 10 new, frequently used data items is created.

[0036] Step 3: When the upper business layer needs to obtain data, it queries according to the ID, and first queries the cache layer of the database. It can directly query the data that meets the query conditions in the newly generated page, and then returns the newly generated page;

[0037] The default method is to query by ID, which is the primary key method. Other types of queries are also supported, but the page page needs to occupy valuable database cache layer resources. If the database cache layer is sufficient, other types of data queries can be supported; if the data is not found, it will be obtained remotely.

[0038] Step 4: Cache the pages obtained by the upper business layer for subsequent queries. This allows the upper layer to query data on a single page and retrieve other data that may be needed later, greatly improving overall query efficiency and system performance.

[0039] The expiration time can be set to a longer time than the default expiration time. For example, it can be set to 5 times the original expiration time. During this time, the page will exist and can be reused with maximum efficiency, repeatedly refreshing the page content instead of repeatedly deleting and creating the page, which consumes more resources.

[0040] See also Figure 2, an optimization system for high-frequency data query, the system includes: a data statistics module, a data migration module, a page query module and a data cache module;

[0041] The data statistics module is used to add a new query data statistics function unit in the bottom layer of the database. The function unit is used to record the frequency of use of a certain data in a page within a unit time. The frequency of the data is also recorded in the cache layer of the database.

[0042] The data migration module is used to create an asynchronous thread, which is used to migrate data from several data statistics modules to a new page to generate a new page;

[0043] The page query module is used to query the cache layer of the database first when the upper business layer needs to obtain data based on the ID query, and can directly query the data that meets the query conditions in the newly generated page, and then return the newly generated page;

[0044] The data cache module is used to cache the pages obtained by the upper business layer for subsequent query use.

[0045] In one embodiment of the present invention, the functional modules in the data statistics module record data in a manner of sorting in reverse order of frequency.

[0046] In one embodiment of the present invention, after a page is cached in a data cache module, the expiration time of the page data in the cache can be customized.

[0047] The present invention provides an optimization method and system for high-frequency data queries. Each time a data query is performed, statistics are compiled on the data queried over a period of time. For high-volume data queries, data on a page is migrated and a new page is generated to provide faster access. Because the page is already stored in the database's cache layer, migrating and reorganizing the data on the page does not significantly increase resource consumption. However, for higher-level data queries, the ability to query a single page for other data that may be needed later greatly improves overall query efficiency and enhances system performance.

[0048] The above description is only a preferred embodiment of the present invention. All equivalent changes and modifications made according to the scope of the patent application of the present invention should fall within the scope of the present invention.

Claims

1. A high-frequency data query optimization method, characterized in that: The method steps are as follows: Step 1: Add a new function module for querying data statistics in the bottom layer of the database. The function module is used to record the frequency of all data usage within a unit time and record the data frequency in the cache layer of the database; Step 2: Create an asynchronous thread to migrate several pieces of data from step 1 to a new page, generating a new page. Step 3: When the upper business layer needs to obtain data, it queries according to the ID, and first queries the cache layer of the database. It can directly query the data that meets the query conditions in the newly generated page, and then returns the newly generated page; Step 4: Cache the page obtained by the upper business layer for subsequent queries.

2. The optimization method for high-frequency data query according to claim 1, characterized in that: The functional module in step 1 records data in a manner of sorting in reverse order of frequency.

3. The optimization method for high-frequency data query according to claim 1, characterized in that: After the page is cached in step 4, the expiration time of the page data in the cache can be customized.

4. An optimization system for high-frequency data query, characterized in that: The system includes: a data statistics module, a data migration module, a page query module and a data cache module; The data statistics module is used to add a new query data statistics function unit in the bottom layer of the database. The function unit is used to record the frequency of use of a certain data in a page within a unit time. The frequency of the data is also recorded in the cache layer of the database. The data migration module is used to create an asynchronous thread, which is used to migrate data from several data statistics modules to a new page to generate a new page; The page query module is used to query the cache layer of the database first when the upper business layer needs to obtain data based on the ID query, and can directly query the data that meets the query conditions in the newly generated page, and then return the newly generated page; The data cache module is used to cache the pages obtained by the upper business layer for subsequent query use.

5. The high-frequency data query optimization system according to claim 4, characterized in that: The functional module in the data statistics module records data in a manner of sorting in reverse order of frequency.

6. The high-frequency data query optimization system according to claim 4, characterized in that: After the page is cached in the data cache module, the expiration time of the page data in the cache can be customized.

Images