Semantic analysis multi-level caching mechanism for heterogeneous database in wide area environment

Abstract

The invention discloses a semantic analysis multi-level cache mechanism oriented to a heterogeneous database in a cloud edge end environment, and aims to realize recognition of database operation and extraction of types and influence ranges of database operation statements through a mode matching technology so as to construct a unified database access model and realize accurate forwarding and execution of the heterogeneous database operation. Comprising an SQL statement recognition module, a multi-level cache queue, a cache item positioning index and a publishing and subscribing module. And the SQL statement identification module is used for receiving and analyzing metadata of the database and the SQL statements, determining a query type and managing cache update. And the multi-level cache queue dynamically adjusts cache items according to the access frequency and the access time, so that the validity and the accuracy of the cache are ensured. The cache item positioning index accelerates cache access through a hash table and a prefix tree, and the access efficiency is improved. And the publishing and subscribing module is used for maintaining the consistency of caches among different data centers and realizing synchronous updating of the caches through a publishing and subscribing mechanism. The resource utilization rate and the data access performance are improved.

Description

Technical Field

[0001] The present invention relates to a semantic analysis multi-level cache mechanism for heterogeneous databases in a wide area environment, which uses an abstract database access model to construct a unified access method for each heterogeneous database and realizes accurate forwarding and execution of database operations. The present invention belongs to the field of data technology. Background Art

[0002] With the continuous acceleration of digital transformation, especially in the fields of smart manufacturing, smart transportation and automated control, the amount of data generated worldwide has shown explosive growth. These data are characterized by diversity and complexity, including a large amount of structured, semi-structured and unstructured data, distributed across multiple devices and geographical locations, forming a complex wide-area collaborative computing environment. In this context, data not only needs to flow between terminals, edge devices and the cloud, but also faces the problem of how to effectively store and access it.

[0003] In order to adapt to the diverse data storage needs, different industries have adopted various types of database systems, such as relational databases that support transaction processing and document databases that can process semi-structured data. In addition, there are key-value databases for caching and full-text search databases optimized for large-scale data analysis. These databases are each optimized for specific fields, but there are significant differences in access and operation between different databases, resulting in developers having to learn and adapt to different operation interfaces and query languages ​​in actual applications. The complexity of cross-database operations greatly increases development costs and significantly increases the difficulty of system maintenance.

[0004] In addition, the network latency problem of heterogeneous databases distributed in wide area networks also exacerbates the decline in query performance. Especially when it is necessary to span multiple geographical regions, maintaining data consistency, improving access efficiency, and meeting real-time requirements have become core challenges. In order to improve system response speed, most solutions currently choose to reduce access latency through multi-level caching and asynchronous computing, but existing caching strategies are usually not targeted and cannot effectively handle the complex semantics and data characteristics of heterogeneous databases.

[0005] There are already some technical solutions that attempt to provide a unified access method for different database types. For example, some database platforms extend external data access interfaces so that applications can query from multiple heterogeneous data sources. However, such solutions have limitations in performance and scalability, especially in highly distributed environments, where network instability and high latency remain urgent issues. At the same time, the operating modes of different databases vary greatly, and the existing unified interfaces often cannot cover the specific operating requirements of the database, further limiting the flexibility of operations.

[0006] In order to solve the above problems, this paper proposes a multi-level cache mechanism based on semantic analysis to improve the access efficiency in wide-area heterogeneous database environments. The core idea of ​​this mechanism is to extract the specific type and scope of query operations through semantic recognition of SQL statements, and then update and manage the cache content in a targeted manner. Combining factors such as access frequency and time, this paper designs an optimized cache elimination algorithm to effectively reduce redundant data and access delays. In addition, through the publish-subscribe method, the system can maintain cache consistency between different data centers, thereby ensuring the accuracy and efficiency of queries in a large-scale distributed environment.

[0007] In summary, although the existing database access mechanism has certain applications in certain specific scenarios, it still has problems such as low access efficiency, high operational complexity, and difficulty in maintaining data consistency in a wide-area distributed environment. The multi-level cache mechanism proposed in this paper improves the query performance of heterogeneous databases through semantic analysis and cache management of query operations, and effectively reduces the development and maintenance costs of the system. Summary of the invention

[0008] This paper proposes a semantic multi-level caching mechanism. The main idea is to identify database operations through pattern matching technology, extract the type and impact scope of database operation statements, and update the query result set cache. In order to improve the effectiveness and accuracy of the cache and reduce the consistency maintenance cost of the query result cache, this mechanism adopts a cache elimination mechanism that combines access frequency and access time and a cache expiration mechanism based on the publish-subscribe model, thereby achieving database access acceleration in a wide area environment.

[0009] The technical solution of the present invention is as follows:

[0010] A multi-level caching mechanism for semantic analysis of heterogeneous databases in a wide-area environment, including:

[0011] The SQL statement identification module is used to receive metadata of the database and SQL statements, identify the type and scope of the query statement, obtain the request type, field information, table and database information, conditional query information, grouping information, and paging information through metadata extraction, and locate the database connection that needs to perform data access through metadata matching;

[0012] The multi-level cache queue module is used to store specific cache items, manage the cache according to the recognition results of the SQL statement recognition module, and run background tasks to recycle cache items regularly;

[0013] Cache item location index is used to speed up cache access. The multi-level queue contains a data structure consisting of a hash table and a prefix tree. The hash table is divided into three layers. The keys of each layer of the hash table are the database name, the entity database name, and the table name. The prefix tree is used to store the data table field name.

[0014] The publish-subscribe module is used to communicate with systems deployed in other data centers, maintain cache consistency, and accept messages at the table granularity. When the SQL statement recognition module recognizes a data query, it passes the table information to the publish-subscribe module. The publish-subscribe module subscribes to modifications to other data centers based on the table information. When the SQL statement recognition module recognizes a data modification, it passes the table information and SQL statement to the publish-subscribe module. The publish-subscribe module pushes the SQL statement to the subscriber based on the table information.

[0015] The SQL statement recognition module executes the logic of setting cache when identifying a query statement, executes the logic of deleting cache when identifying a delete, modify, or add statement, and calculates the scope of influence of the corresponding statement, and deletes the cache within the smallest scope.

[0016] The multi-level cache queue module includes at least two cache queues, which are numbered 1 to n from high to low according to the queue priority. The cache items are placed in queue 1, and the multi-level cache queue takes over the cache item management, and downgrades or promotes between queues according to the data access frequency and access time until they are eliminated or promoted to queue 1.

[0017] The cache item location index accelerates cache access by combining a hash table and a prefix tree. The keys of the hash table are the database name, the entity database name and the table name in sequence. The prefix tree is used to store the data table field names and access the prefix tree in sequence to query the cache items.

[0018] When the SQL statement recognition module identifies a data query, the publish-subscribe module passes the table information to the publish-subscribe module. The publish-subscribe module subscribes to modifications to other data centers based on the table information. When data modifications are identified, the table information and SQL statements are passed to the publish-subscribe module. The publish-subscribe module pushes the SQL statement to the subscriber based on the table information.

[0019] The multi-level cache queue module sets a watermark for the cache capacity. When the data of the cache item exceeds the watermark, the system calculates the number of cache items that need to be removed from the cache and shrinks the cache level by level to ensure that the number of cache items in each level of queue is 1.5 times that of the next level.

[0020] An access bit and an access buffer are defined in each cache item. The access bit is used to mark whether the cache item has been accessed within the sampling time. The access buffer is used to save the access bits in the past 16 sampling cycles and evaluate the access status of the cache item through the access frequency and access time. The cache item is moved between queues based on the evaluation results.

[0021] When the cache item location index uses a key-value database such as Redis, a false database name and an entity database name are injected to ensure the uniformity of the data structure.

[0022] Cache item management is divided into two parts. First, when the data involved in the cache is modified, the cache needs to be actively deleted. Second, the cache capacity is limited, and data that is not frequently accessed in the cache needs to be cleaned up regularly. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 This is an architecture diagram of the semantic analysis multi-level cache system involved in the present invention.

[0024] Figure 2 The present invention relates to a cache positioning index schematic diagram. DETAILED DESCRIPTION

[0025] Combined with the attached figure ( Figure 1-Figure 2 ) The present invention is described in further detail.

[0026] like Figure 1 As shown, the overall structure of the cache system is shown. The system includes an SQL statement recognition module, a multi-level cache queue, a cache item positioning index, and a publish-subscribe module.

[0027] The SQL statement recognition module receives the metadata of the database and SQL statements as parameters, and identifies the type and scope of the query statement to be executed. Specifically, the SQL language recognition module can obtain the request type, field information, table and database information, conditional query information, grouping information, paging information, etc. through metadata extraction, and locate the database connection that needs to perform data access through metadata matching. The SQL language recognition module analyzes the statement type. If it is a query statement, it executes the logic of setting the cache, otherwise it executes the logic of deleting the cache. When deleting the cache, the SQL language recognition module will calculate the scope of the corresponding statement and delete the cache within the smallest scope. For example, the execution results of "select name from user" and "select info from user" have been cached in the cache. When executing the statement "update userset name=foo", only the cached result of the first SQL statement needs to be deleted.

[0028] The multi-level cache queue module stores specific cache items, manages the cache according to the recognition results of the SQL language recognition module, and runs background tasks to periodically recycle cache items. Specifically, the multi-level cache queue module contains at least two cache queues, which are numbered 1 to n in descending order according to the queue priority. This method will place the cache item in queue 1; then the multi-level cache queue will take over the cache item management. The cache management is divided into two parts. One is that the data has been modified and the cache has expired. This situation is divided into the SQL language recognition module pushing invalidation information and the remote service pushing invalidation information through the publish-subscribe module; the second is to manage cache items according to data access frequency and access time, downgrading cache items between queues until they are eliminated, or promoting cache items between queues until queue 1.

[0029] Traversing the multi-level queue to access the cache is extremely time-consuming, so this method designs a cache item location index to speed up cache access. The multi-level queue contains a data structure composed of a hash table and a prefix tree, where the hash table is divided into three layers. Figure 2 As shown, the keys of each layer of hash tables are database name, entity database name, and table name in turn; except for the last layer, the value of each layer of hash tables is the next layer of hash tables; the value of the last layer is a prefix tree composed of the data table field names. When caching data, the cache item positioning index will sort the fields and build a prefix tree in this order. For example, when accessing the cache result of "select name,info from`127.0.0.1:3306`.db.user", all entity databases in this database will be queried according to 127.0.0.1:3306 first, then all tables in this entity database will be queried according to db, and then the corresponding prefix tree will be queried according to user, and finally the fields will be sorted to obtain the sequence of "info" and "name" and the prefix tree will be accessed in this order to query the cache item. It is worth noting that when using a key-value database such as Redis, this method will inject false database names and entity database names to ensure the uniformity of the data structure.

[0030] The publish-subscribe module is responsible for communicating with systems deployed in other data centers and maintaining cache consistency. The publish-subscribe module receives messages at the table granularity. When the SQL statement recognition module recognizes a data query, it passes the table information to the publish-subscribe module, and the publish-subscribe module subscribes to modifications to other data centers based on the table information. When the SQL statement recognition module recognizes a data modification, it passes the table information and SQL statement to the publish-subscribe module, and the publish-subscribe module pushes the SQL statement to the subscriber based on the table information. For example, the current program is deployed in two data centers. When data center A executes "select name from user", it will cache the statement execution results and subscribe to the changes to the table "user" from data center B; when data center B executes the statement "update user set name=foo", it will push this SQL statement to data center A, and the cached results in data center A will expire.

[0031] When the cache item management of this method is divided into two parts, one is that the data involved in the cache is modified, and the data in the cache is no longer the latest data and needs to be actively deleted; the other is that the capacity of the cache is limited, and the data that is not frequently accessed in the cache needs to be cleaned up regularly.

[0032] When sending a request, the SQL statement will go through two processes: metadata extraction and metadata matching. Metadata extraction can obtain the request type, field information, table and database information, conditional query information, grouping information, paging information, etc., and metadata matching can locate the database connection that needs to perform data access. Then, identify the type of SQL statement. If it is a query statement, no cache expiration processing is performed; if it is a delete, modify, or add statement, the impact range of the SQL statement is further determined. When the SQL statement type is delete or add, this method uses the cache item positioning index to find the cache items that may be affected at the table granularity and clears all cache items. For example, the execution results of "select namefrom user" and "select info from user" are cached in the cache. When the statement "insert user value(foo,bar)" is executed, all cached results of the "user" table are deleted. When the SQL statement type is modification, this method uses the cache item positioning index to find the cache items that may be affected at the field granularity, and further compares the query conditions to minimize the number of cache items deleted. For example, the cache caches the execution results of "select info from user", "select namefrom user where id=1", and "select name from user where id=2". When the statement "update user set name=foo where id=2" is executed, only the execution result of "select name fromuser where id=2" is deleted.

[0033] On the other hand, this method ensures the effectiveness of the cache by moving cache items between queues by evaluating the access frequency and access time of cache items. First, this method defines an access bit and an access buffer in each cache item. If the cache item is accessed within the sampling time, the access bit will be set to 1, otherwise it will be 0; the size of the access buffer is set to 16 to save the access bits in the past 16 sampling cycles. Secondly, this method defines a timer task to periodically check the access bit of the cache item. The timer task moves the value of the access bit to the end of the access buffer and deletes the earliest access record.

[0034] By recording the values ​​in the access cache, this method uses the following formula to calculate the access status of the evaluation cache item:

[0035]

[0036] Where s represents the evaluation result of the cache item, i represents the index of the data in the access buffer, and ki represents the value in the access cache, which can only be 0 or 1. The above formula shows that this method groups the data in the access cache into groups of 4. The data in the same group has the same weight value, and the latest access record has the highest weight. Through this formula, this method uniformly evaluates the access frequency and access time, and comprehensively considers the possibility of the cache item being accessed in the future. When the evaluation result s is the same, this method believes that the cache item with an access time closer to the present time is more important. For example, the access sequences of two cache items are "00000000000000010" and "0000000000000001", and their evaluation results are the same, but the second cache item is more important.

[0037] In this method, each level of queue is set to 1.5 times the size of the next set of queues, and the total number of cache items in all queues is specified, so the cache capacity is limited. This method sets a watermark for the cache capacity. When the data of the cache item exceeds the watermark, this method will reclaim the cache space. Specifically, the system will first calculate the number of cache items that need to be removed from the cache, and then remove the corresponding number of cache items from the last level of cache; then the cache will be shrunk level by level to ensure that the number of cache items in each level of queue is 1.5 times that of the next level.

[0038] When the evaluation result of a cache item is recalculated and is greater than the original value, this method compares its evaluation value with the minimum evaluation value in the previous queue. If the evaluation value of the current cache item is larger, the cache item is promoted to the previous queue.

Claims

1. A semantic analysis multi-level cache mechanism for heterogeneous databases in a wide area environment, characterized by: include: The SQL statement identification module is used to receive metadata of the database and SQL statements, identify the type and scope of the query statement, obtain the request type, field information, table and database information, conditional query information, grouping information, and paging information through metadata extraction, and locate the database connection that needs to perform data access through metadata matching; The multi-level cache queue module is used to store specific cache items, manage the cache according to the recognition results of the SQL statement recognition module, and run background tasks to recycle cache items regularly; Cache item location index is used to speed up cache access. The multi-level queue contains a data structure consisting of a hash table and a prefix tree. The hash table is divided into three layers. The keys of each layer of the hash table are the database name, the entity database name, and the table name. The prefix tree is used to store the data table field name. The publish-subscribe module is used to communicate with systems deployed in other data centers, maintain cache consistency, and accept messages at the table granularity. When the SQL statement recognition module recognizes a data query, it passes the table information to the publish-subscribe module. The publish-subscribe module subscribes to modifications to other data centers based on the table information. When the SQL statement recognition module recognizes a data modification, it passes the table information and SQL statement to the publish-subscribe module. The publish-subscribe module pushes the SQL statement to the subscriber based on the table information.

2. The semantic analysis multi-level cache mechanism according to claim 1 is characterized in that: The SQL statement recognition module executes the logic of setting cache when identifying a query statement, executes the logic of deleting cache when identifying a delete, modify, or add statement, and calculates the scope of influence of the corresponding statement, and deletes the cache within the smallest scope.

3. The semantic analysis multi-level cache mechanism according to claim 1 is characterized in that: The multi-level cache queue module includes at least two cache queues, which are numbered 1 to n from high to low according to the queue priority. The cache items are placed in queue 1, and the multi-level cache queue takes over the cache item management, and downgrades or promotes between queues according to the data access frequency and access time until they are eliminated or promoted to queue 1.

4. The semantic analysis multi-level cache mechanism according to claim 1 is characterized in that: The cache item location index accelerates cache access by combining a hash table and a prefix tree. The keys of the hash table are the database name, the entity database name and the table name in sequence. The prefix tree is used to store the data table field names and access the prefix tree in sequence to query the cache items.

5. The semantic analysis multi-level cache mechanism according to claim 1 is characterized in that: When the SQL statement recognition module identifies a data query, the publish-subscribe module passes the table information to the publish-subscribe module. The publish-subscribe module subscribes to modifications to other data centers based on the table information. When data modifications are identified, the table information and SQL statements are passed to the publish-subscribe module. The publish-subscribe module pushes the SQL statement to the subscriber based on the table information.

6. The semantic analysis multi-level cache mechanism according to claim 3 is characterized in that: The multi-level cache queue module sets a watermark for the cache capacity. When the data of the cache item exceeds the watermark, the system calculates the number of cache items that need to be removed from the cache and shrinks the cache level by level to ensure that the number of cache items in each level of queue is 1.5 times that of the next level.

7. The semantic analysis multi-level cache mechanism according to claim 3 is characterized in that: An access bit and an access buffer are defined in each cache item. The access bit is used to mark whether the cache item has been accessed within the sampling time. The access buffer is used to save the access bits in the past 16 sampling cycles and evaluate the access status of the cache item through the access frequency and access time. The cache item is moved between queues based on the evaluation results.

8. The semantic analysis multi-level cache mechanism according to claim 1 is characterized in that: When the cache item location index uses a key-value database such as Redis, a false database name and an entity database name are injected to ensure the uniformity of the data structure.

9. The semantic analysis multi-level cache mechanism according to claim 1 is characterized in that: Cache item management is divided into two parts. First, when the data involved in the cache is modified, the cache needs to be actively deleted. Second, the cache capacity is limited, and data that is not frequently accessed in the cache needs to be cleaned up regularly.

Images