Method and system for optimizing segmented query data
By setting up the database proxy layer during database access, reading operations are processed in segments, precise query classes are processed in blocking waiting and local cache priority queries, and scope query classes are processed in split processing and local cache merging, which solves the problem of poor database reading performance and improves data query efficiency and system performance.
Patent Information
- Application Number
- CN202510036521.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-01-09
- Publication Date
- 2025-05-06
AI Technical Summary
In the prior art, the reading data performance is poor during database access, especially in scenarios where more reads and less writes, it is difficult to effectively improve the data query efficiency.
An optimization method and system for segmented data query is adopted to process read operations by setting up a database proxy layer. For precise query classes, blocking waiting and local cache priority query are adopted; for scope query classes, split processing and local cache merge are adopted to reduce duplicate data queries.
It effectively improves the efficiency of data query, reduces the query pressure of the database, and improves the overall system performance, especially in the read request scenario where duplicate data range exists.
Smart Images
Figure CN119938712A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of computer technology, and in particular to an optimization method and system for segmented query data. Background Art
[0002] Commonly found in modern internet systems, databases are fundamental components whose primary function is to store data. With the rapid growth of users, the volume of user requests has also increased dramatically, while the number of database connections is limited. Currently, multi-threaded concurrent access is being used to improve database data access capabilities, including both reading and writing data. In real-world scenarios, most systems have more reads than writes, so designing a database that can improve data retrieval in read scenarios is crucial. Summary of the Invention
[0003] In order to overcome the problem of poor data reading performance during database access in the prior art, the purpose of the present invention is to provide an optimization method and system for segmented query data, which can improve data query efficiency.
[0004] The present invention is implemented by the following scheme:
[0005] An optimization method for segmented query data, the method steps are as follows:
[0006] Step 1: Set up a database proxy layer. All database requests are forwarded by the database proxy layer. If the request is a write request, the database proxy layer directly forwards the write request to the database for operation. If the request is a read operation, the processing described in this article is performed.
[0007] Step 2: In the read operation scenario, the query SQL statement is parsed. When querying a single data item, it is represented as an exact query; when querying with a certain range condition, it is represented as a range query.
[0008] Step 3: When the query SQL statement is a precise query, once a request has been forwarded to the database within a unit of time, other precise query requests will be blocked by the database proxy layer and wait for the database to return the result data. The database proxy layer will then fill in the data and return it.
[0009] Step 4: When the query SQL statement is a range query, if there are multiple requests, each request is processed in sequence and recorded in the local memory. When processing subsequent requests, if the current query data range overlaps with the data range queried in the cache, the overlapping data in the local memory is first called, and then the data in the non-overlapping data range is queried. Finally, the data is aggregated and returned separately.
[0010] Furthermore, step 3 is as follows: each piece of data will have a unique primary key ID, with the table name + unique primary key ID as the key. Within a unit time, when data with a certain key reaches the database proxy layer, it is pre-locked. After the lock is completed, the data source data is queried. When a data request with this key arrives again within a unit time, it will wait because it is waiting for the lock. After the request obtains the data, it is loaded into the local cache. If subsequent requests access data with the same key, after the current request obtains the lock again, the local cache is directly queried to obtain the data.
[0011] A segmented query data optimization system, comprising: a database proxy layer setting module, a read operation parsing module, a precise query processing module, and a range query processing module;
[0012] The database proxy layer setting module is used to set up a database proxy layer, and all database requests are forwarded by the database proxy layer; when it is a write request, the database proxy layer directly forwards the write request to the database for operation; when it is a read operation, the processing of this article is performed;
[0013] The read operation parsing module is used to parse the query SQL statement in the read operation scenario. When it is a single data query, it represents the exact query class; when it is a query with a certain range condition, it represents the range query class;
[0014] The precise query processing module is used to block other precise query requests by the database proxy layer when the query SQL statement is a precise query. After a request has been forwarded to the database within a unit of time, the database proxy layer will fill in the data and return it after the database returns the result data.
[0015] The range query class processing module is used to process each request in sequence when the query SQL statement is a range query class, if there are multiple requests, and record them in the local memory. When processing subsequent requests, if the current query data range overlaps with the data range that has been queried in the cache, the overlapping data in the local memory is first called, and then the data in the non-overlapping data range is queried, and finally the data is aggregated and returned separately.
[0016] Furthermore, the precise query processing module is specifically as follows: each piece of data will have a unique primary key id, with the table name + unique primary key id as the key. Within a unit time, when the data of a certain key reaches the database proxy layer, it will be locked in advance, and after the locking is completed, the data source data will be queried; and when the data request for this key arrives again within a unit time, it will wait because of waiting for the lock; after the request obtains the data, it is loaded into the local cache. If the data of the same key is accessed in subsequent requests, the local cache will be directly queried to obtain the data after the current request obtains the lock again.
[0017] The beneficial effects of the present invention are:
[0018] The present invention provides an optimization method and system for segmented query data, which adopts a database query database proxy layer method to further improve the database's ability to read data; in a write scenario, the database proxy layer directly forwards the data to the database for processing; in a read scenario, for range read data scenarios, the database proxy layer performs special conversion processing, and in a unit time, multiple request scenarios, the requests for range data queries are split and processed; when the time ranges of some request queries overlap, only the non-overlapping data are queried, and the database proxy layer performs the final data merging operation. This method can effectively improve data query efficiency, reduce the overall query pressure of the database, and improve overall system performance in read request scenarios with duplicate data ranges. BRIEF DESCRIPTION OF THE DRAWINGS
[0019] Figure 1 is a flow chart of the method of the present invention;
[0020] Figure 2 It is a structural block diagram of the system of the present invention. DETAILED DESCRIPTION
[0021] The present invention will be further described below with reference to the accompanying drawings.
[0022] See also Figure 1 , an optimization method for segmented query data, the method steps are as follows:
[0023] Step 1: Set up a database proxy layer. All database requests are forwarded by the database proxy layer. If the request is a write request, the database proxy layer directly forwards the write request to the database for operation. If the request is a read operation, the processing described in this article is performed.
[0024] Step 2: In the read operation scenario, the query SQL statement is parsed. When querying a single data item, it is represented as an exact query; when querying with a certain range condition, it is represented as a range query.
[0025] Step 3: When the query SQL statement is a precise query, once a request has been forwarded to the database within a unit of time, other precise query requests will be blocked by the database proxy layer and wait for the database to return the result data. The database proxy layer will then fill in the data and return it.
[0026] Step 4: When the query SQL statement is a range query, if there are multiple requests, each request is processed in sequence and recorded in the local memory. When processing subsequent requests, if the current query data range overlaps with the data range queried in the cache, the overlapping data in the local memory is first called, and then the data in the non-overlapping data range is queried. Finally, the data is aggregated and returned separately.
[0027] The present invention will be further described below with reference to a specific embodiment:
[0028] A method for optimizing segmented query data, the method comprising the following steps:
[0029] Step 1: A database proxy layer exists, which forwards all database requests. Write requests are forwarded directly to the database for processing. Read requests are handled with the special processing described in this article.
[0030] Step 2: In the read scenario, the query SQL needs to be parsed. There are two main types of queries: precise queries for single data entries, such as queries based on the primary key ID or a combination of query conditions; and range queries for queries based on a certain range of conditions.
[0031] Step 3: Assuming the unit time is 1 second, if the SQL query statement is a precise query, once a request has been forwarded to the database within the unit time, other similar requests will be blocked by the database proxy layer. After the database returns the result data, the database proxy layer will fill in the data and return it. This method can effectively reduce the number of precise query requests.
[0032] That is, in a concurrent access scenario, only one request will be sent to the data source to query data. Each piece of data will have a unique primary key ID, which is the table name + the unique primary key ID. This data key ensures data uniqueness.
[0033] For example, within 100 milliseconds, when data for a certain key arrives at the database proxy layer, it is pre-locked. After the lock is completed, the data source data query is performed. If a data request for the same key arrives again within the same time period, it will wait because it needs to wait for the lock.
[0034] After the first lock request, other requests wait. When the request obtains the data, it is loaded into the local cache. When other requests with the same key obtain the lock again, they directly query the local cache instead of going to the data source again to obtain the data.
[0035] Step 4: When the SQL query statement is a range query, if the query data ranges of multiple requests overlap within a unit time, the data will be acquired, aggregated, and returned separately, which can effectively reduce the amount of duplicate data acquired.
[0036] For example, if request A queries for userIds ranging from 1 to 100, request A is sent to the database for data query, retrieves data for 1-100, and stores it in local memory. When request B arrives, querying for userIds ranging from 50 to 150, it pre-queries local memory and finds that a request has already been made to retrieve data for 1-100 within a certain timeframe. The SQL is then automatically modified, changing the query from the original 50-150 range to a 100-150 range. Request B then modifies the SQL to only query for data in the userId range of 100-150. After the results of the two range queries are returned, the database proxy layer aggregates the returned data. At this point, request A's data remains unprocessed, while request B's data is aggregated from two parts: the data for 50-100 read from the local cache and the data for 100-150 retrieved from the data source. The aggregated data for 50-150 is then returned to the client corresponding to request B. This approach effectively reduces the number of range queries.
[0037] See also Figure 2 , an optimization system for segmented query data, the system comprising: a database proxy layer setting module, a read operation parsing module, a precise query processing module and a range query processing module;
[0038] The database proxy layer setting module is used to set up a database proxy layer, and all database requests are forwarded by the database proxy layer; when it is a write request, the database proxy layer directly forwards the write request to the database for operation; when it is a read operation, the processing of this article is performed;
[0039] The read operation parsing module is used to parse the query SQL statement in the read operation scenario. When it is a single data query, it represents the exact query class; when it is a query with a certain range condition, it represents the range query class;
[0040] The precise query processing module is used to block other precise query requests by the database proxy layer when the query SQL statement is a precise query. After a request has been forwarded to the database within a unit of time, the database proxy layer will fill in the data and return it after the database returns the result data.
[0041] The range query class processing module is used to process each request in sequence when the query SQL statement is a range query class, if there are multiple requests, and record them in the local memory. When processing subsequent requests, if the current query data range overlaps with the data range that has been queried in the cache, the overlapping data in the local memory is first called, and then the data in the non-overlapping data range is queried, and finally the data is aggregated and returned separately.
[0042] In one embodiment of the present invention, the precise query processing module is specifically as follows: each piece of data will have a unique primary key id, with the table name + unique primary key id as the key. Within a unit time, when the data of a certain key reaches the database proxy layer, it is locked in advance, and after the locking is completed, the data source data is queried; and when the data request for this key arrives again within a unit time, it will wait because of waiting for the lock; after the request obtains the data, it is loaded into the local cache. If the data of the same key is accessed in subsequent requests, the local cache will be directly queried to obtain the data after the current request obtains the lock again.
[0043] 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 method for optimizing segmented query data, characterized in that: The method steps are as follows: Step 1: Set up a database proxy layer, and all database requests are forwarded by the database proxy layer; when it is a write request, the database proxy layer directly forwards the write request to the database for operation; when it is a read operation, the processing in this article is performed; Step 2: In the read operation scenario, the query SQL statement is parsed. When a single data query is performed, it is represented as an exact query type; when a query is performed with a certain range condition, it is represented as a range query type; Step 3: When the query SQL statement is a precise query, once a request has been forwarded to the database within a unit time, other precise query requests will be blocked by the database proxy layer. After the database returns the result data, the database proxy layer will fill in the data and return it. Step 4: When the query SQL statement is a range query, if there are multiple requests, each request is processed in turn and recorded in the local memory. When processing subsequent requests, if the current query data range overlaps with the queried data range in the cache, the overlapping data in the local memory is called first, and then the data in the non-overlapping data range is queried. Finally, the data is aggregated and returned separately.
2. The optimization method for segmented query data according to claim 1, characterized in that: Step 3 is as follows: Each piece of data will have a unique primary key id, with table name + unique primary key id as the key. Within a unit of time, when the data of a certain key reaches the database proxy layer, it will be locked in advance. After the locking is completed, the data source data will be queried; When a data request for this key arrives again within a unit of time, it will wait because of waiting for the lock; after the request obtains the data, it is loaded into the local cache. If the data of the same key is accessed in subsequent requests, after the current request obtains the lock again, it will directly query the local cache to obtain the data.
3. An optimization system for segmented query data, characterized in that: The system includes: a database proxy layer setting module, a read operation parsing module, a precise query processing module and a range query processing module; The database proxy layer setting module is used to set a database proxy layer, and all database requests are forwarded by the database proxy layer; when it is a write request, the database proxy layer directly forwards the write request to the database for operation; when it is a read operation, the processing of this article is performed; The read operation parsing module is used to parse the query SQL statement in the read operation scenario. When a single data query is performed, it represents an exact query class; when a query is performed with a certain range condition, it represents a range query class; The precise query processing module is used for when the query SQL statement is a precise query type, when there is a request forwarded to the database within a unit time, other precise query type requests will be blocked and waited by the database proxy layer, After waiting for the database to return the result data, the database proxy layer fills in the data and returns it; The range query class processing module is used to process each request in sequence and record them in the local memory when there are multiple requests in the query SQL statement of the range query class. When processing subsequent requests, if the current query data range overlaps with the data range that has been queried in the cache, the overlapping data in the local memory is first called, and then the data in the non-overlapping data range is queried, and finally the data is aggregated and returned separately.
4. The optimization system for segmented query data according to claim 3, characterized in that: The precise query processing module is specifically as follows: each piece of data will have a unique primary key id, table name + unique primary key id as the key, within a unit time, when a key data reaches the database proxy layer, it is locked in advance, and after the locking is completed, the data source data is queried; When a data request for this key arrives again within a unit of time, it will wait because of waiting for the lock; after the request obtains the data, it is loaded into the local cache. If the data of the same key is accessed in subsequent requests, after the current request obtains the lock again, it will directly query the local cache to obtain the data.