A query method based on result set query cache in an openGauss database

Abstract

The application relates to the technical field of databases and provides a query method based on result set query caching in an openGauss database, which comprises the following steps: performing semantic analysis on a query statement, judging whether the result set query caching can be enabled according to the type of the query statement; calculating the hash value of the query statement capable of enabling the result set query caching, judging whether the hash value of the query statement hits a cache record in a cache hash table; when the hash value of the query statement hits a cache record in the cache hash table, judging whether the hit cache record is available at a current time node, returning the cache record available at the current time node to a client initiating the query; when the hash value does not hit a cache record in the cache hash table, synchronously newly creating an initialization cache during query execution, filling the initialization cache with cache content, and adding an effective cache into the result set query caching. The application can reduce repeated calculation of the same statement and improve the average response time of the database.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and in particular to a query method based on result set query cache in an openGauss database. BACKGROUND

[0002] A query statement needs to go through complex processes such as parsing, planning, and execution from execution to returning a result set to a client. For query statements that use a large amount of data for complex operations, their execution time is affected by various factors such as system IO read / write, database calculation, network transmission communication, etc., making it difficult to complete the entire processing flow and return the result in a short time. In actual business scenarios, an in-memory data structure storage system similar to Redis is usually used as middleware between the business system and the database to reduce the pressure on the database and improve the business response speed. However, due to the large amount of calculation involved in complex business scenarios, even if some middleware is used to reduce the load on the database, in a high-concurrency scenario, a large number of query statements still need to be processed by the database.

[0003] The larger the amount of data used by a query statement, the more complex functions used, and the more complex the join conditions, the longer the execution time required. If the database instances accessed by the same query statement multiple times and all the actual data accessed by the query statement do not change in multiple queries, and the result set of the query does not change because the data has not changed, then each query after the first query does not need to perform repeated scanning of the disk, calculation, etc., which will save time.

[0004] Currently, Shared Buffers have been implemented in openGauss. Shared Buffers is an in-memory caching mechanism used to store frequently accessed data blocks in the database. These data blocks exist as an equivalent mapping of actual files in memory. Using Shared Buffers in queries can reduce a portion of disk access operations, but it cannot reduce the calculation of these data, and it cannot guarantee that all data accessed by a query is already stored in Shared Buffers.

[0005] Result set caching can avoid repeated data calculation by the database for the same query statement, thereby reducing the load and response time of the database. When the data in the database changes, the query cache will automatically expire, thereby ensuring the correctness of the query result. However, openGauss does not support caching of query result sets.

[0006] Therefore, how to apply result set caching compatibly in openGauss to improve the query efficiency of openGauss database has become a technical problem to be solved. SUMMARY

[0007] Therefore, in order to overcome the deficiencies of the prior art, the present application aims to provide a query method based on result set query cache in an openGauss database.

[0008] In one aspect, the present application provides a query method based on result set query cache in an openGauss database, comprising:

[0009] performing semantic analysis on the query statement, and determining whether the result set query cache can be enabled according to the type of the query statement;

[0010] calculating the hash value of the query statement for which the result set query cache can be enabled, and determining whether the hash value of the query statement hits a cache record in a cache hash table;

[0011] when the hash value of the query statement hits a cache record in the cache hash table, determining whether the hit cache record is available at the current time node, and returning the cache record available at the current time node to the client initiating the query;

[0012] when the hash value does not hit a cache record in the cache hash table, synchronously creating an initialization cache during query execution, filling the initialization cache with cache content, and adding the effective cache to the result set query cache.

[0013] Preferably, in the query method based on result set query cache in the openGauss database of the present application, the semantic analysis on the query statement and the determination of whether the result set query cache can be enabled according to the type of the query statement comprise:

[0014] when the type of the query statement is DDL or DML, the query statement is determined to be unable to enable the result set query cache;

[0015] when the type of the query statement is DQL, it is determined whether the query statement satisfies the constraints of the result set query cache, and when the query statement satisfies the constraints of the result set query cache, the query statement is determined to be able to enable the result set query cache.

[0016] Preferably, in the query method based on result set query cache in the openGauss database of the present application, when the type of the query statement is DDL or DML, the query statement is determined to be unable to enable the result set query cache, comprising:

[0017] determine whether the query statement of the DDL or DML type has an influence on the accuracy of the query result, when the query statement of the DDL or DML type has an influence on the accuracy of the query result, record the table OID used by the query statement during semantic analysis of the query statement, invalidate the cache record dependent on the table OID in the cache hash table during query execution, and obtain query data from the storage engine and return to the client initiating the query;

[0018] when the query statement of the DDL or DML type has no influence on the accuracy of the query result, obtain query data from the storage engine and return to the client initiating the query.

[0019] Preferably, in the query method based on the result set query cache in the openGauss database, the hash value of the query statement capable of enabling the result set query cache is calculated, including: obtaining the query tree key structure of the query statement capable of enabling the result set query cache, extracting the node information of the query tree from the query tree key structure, generating a character sequence according to the extracted node information, and calculating the hash value of the character sequence.

[0020] Preferably, in the query method based on the result set query cache in the openGauss database, whether the hash value of the query statement hits the cache record in the cache hash table is determined, including:

[0021] The cache hash table is divided into a plurality of hash buckets, the hash value of the query statement is taken modulo according to the number of hash buckets, and the modulo hash value is matched in the hash bucket;

[0022] When the hash value of the query statement matches the cache record in the cache hash table, the hash value of the query statement is determined to hit the cache record in the cache hash table.

[0023] When the hash value of the query statement does not match the cache record in the cache hash table, the hash value of the query statement is determined to not hit the cache record in the cache hash table.

[0024] Preferably, in the query method based on the result set query cache in the openGauss database, when the hash value of the query statement matches the cache record in the cache hash table, the hash value of the query statement is determined to hit the cache record in the cache hash table, including: comparing whether the query tree structure of the query statement and the query tree structure in the cache record of the hash table are the same, verifying whether the query statement and the cache record in the hash table belong to the same query.

[0025] Preferably, in the query method based on result set query cache in the openGauss database of the application, when the hash value of the query statement hits the cache record in the cache hash table, it is judged whether the hit cache record is available at the current time node, and the cache record available at the current time node is returned to the client initiating the query, comprising:

[0026] When the hit cache record is available at the current time node, the cache execution node is used to convert the cache record into a TUPLE structure recognizable by the query executor;

[0027] When the hit cache record is not available at the current time node, the query data is obtained from the storage engine and returned to the client initiating the query.

[0028] Preferably, in the query method based on result set query cache in the openGauss database of the application, when the hash value does not hit the cache record in the cache hash table, a new initialization cache is created synchronously during query execution, and the cache content is filled into the initialization cache, and the effective cache is added to the result set query cache, comprising:

[0029] A cache record is generated by a placeholder operation, and the cache record in the initialization state is held in the cache hash table;

[0030] During the execution process of the query statement, the query result of each time is continuously filled into the result set corresponding to the cache record in the initialization state in the form of a tuple, and when the query result is filled into the result set, an operation occurs that invalidates the cache, the filling process is terminated, and the context of the query executor gives up holding the cache record;

[0031] When the query statement execution is completed, it is judged again whether an operation that invalidates the cache occurs during the filling process, and when no operation that invalidates the cache occurs, the state of the cache record in the initialization state after filling is updated to be effective.

[0032] Preferably, in the query method based on result set query cache in the openGauss database of the application, a cache record is generated by a placeholder operation, and the cache record in the initialization state is held in the cache hash table, comprising: a reference count is set for each cache, the placeholder operation is performed after the query rewriting stage, the hash bucket hit by the query is locked for memory copying, a cache record held by the cache hash table in the initialization state is generated in the global cache, and the reference count of the cache is updated.

[0033] Preferably, in the query method based on result set query cache in the openGauss database, the cache record in the valid state includes cache key information, and the cache key information includes a cache record ID, a query tree complete structure, a query tree hash value, and a result set stored in the form of a tuple.

[0034] Preferably, the query method based on result set query cache in the openGauss database further includes invalidating the result set query cache.

[0035] Preferably, the query method based on result set query cache in the openGauss database includes the following invalidating the result set query cache.

[0036] When some or all of the cache records are not needed, the invalid cache record ID is specified through a function interface, the hash value of the corresponding query statement is matched in the auxiliary information cache, a certain cache record in the hash table is matched using the hash value and the cache record ID, the reference count value is reduced, and the cache record is deleted from the cache through manual invalidation;

[0037] When the data in the cache expires or the table structure changes, causing the result set in the cache to be inconsistent with the actual result that should be queried out, the automatic invalidation operation is triggered when responding to some DDL or DML statements that have an impact on the accuracy of the query result, the transaction in which the statement is located is in the process of committing, and the cache records related to the OID of the table referenced by all statements in the transaction are invalidated before the transaction is actually committed;

[0038] When the cache space is insufficient, the reference count of the cache is set to the initial value, and the corresponding data in the cache is automatically deleted.

[0039] Finally, the application also provides a computer device, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the query method based on result set query cache in the openGauss database when executing the program.

[0040] The query method based on result set query cache in the openGauss database can reduce repeated calculation of the same statement, directly apply the result set query cache stored in the cache when the actual data of the table accessed by the statement does not change, save the time of accessing the disk and calculation, and support multiple sessions to access the same result content and support the extended query protocol of the openGauss, thereby greatly improving the average response time of the database. It is especially suitable for business scenarios with a large number of concurrent queries and high QPS (Queries Per Second) requirements of the database. Attached Figure Description

[0041] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0042] Figure 1 This diagram illustrates the construction of a result set query cache in the openGauss database based on the result set query cache method of this invention.

[0043] Figure 2 This diagram illustrates the query method based on result set query caching in the openGauss database of this invention, where a result set query cache is constructed before querying.

[0044] Figure 3 This is a flowchart illustrating a query method based on result set query caching in the openGauss database, which is an exemplary embodiment of the present invention.

[0045] Figure 4 This is a schematic diagram of the structure of a cache hash table in a query method based on result set query caching in the openGauss database, which is an exemplary embodiment of the present invention.

[0046] Figure 5 This is a schematic diagram of the cache hash table structure during cache release in a query method based on result set query cache in the openGauss database, which is an exemplary embodiment of the present invention.

[0047] Figure 6 This is a schematic diagram of the structure of the device provided by the present invention. Detailed Implementation

[0048] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0049] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0050] It is important to note that the various aspects of the embodiments described below are within the scope of the appended claims. It should be apparent that the aspects described herein can be embodied in a wide variety of forms and that any specific structure and / or function described herein is merely illustrative. Based on the teachings herein one skilled in the art should appreciate that an aspect described herein can be implemented independently of any other aspects and that an aspect described herein can be implemented both as any claim and as an aspect of a claim. For example, an apparatus can be implemented using any number of the aspects described herein. In addition, an apparatus can be implemented using other structure and / or functionality not expressly described herein. Similarly, a method can be implemented using any number of the aspects described herein. In addition, a method can be implemented using other structure and / or functionality not expressly described herein.

[0051] The technical principle of the present application is as follows:

[0052] Figure 1 The schematic diagram for constructing the result set query cache of the present application is shown in Figure 1 After the client initiates a query, it enters the openGauss database server through the protocol layer, and after the steps of lexical analysis, semantic analysis, query optimization and query execution, the content of the actual data file is obtained in the storage engine. The results are transmitted to the protocol layer by the executor in the query execution stage, and returned to the client by the protocol layer. At the same time, a cache record entry is constructed. The executor stores the result data in the cache structure during the first execution of the query statement, and constructs the result set query cache of this query statement.

[0053] Figure 2 The schematic diagram for querying after constructing the result set query cache of the present application is shown in Figure 2 After constructing the result set query cache, when the client initiates a query, it enters the openGauss database server through the protocol layer, and after the steps of lexical analysis, semantic analysis and query optimization, it first looks up whether there is a cache record corresponding to the query statement in the result set query cache. When there is a cache record corresponding to the query statement, the cache record in the result set query cache is directly transmitted to the protocol layer, and returned to the client by the protocol layer. When the same query is initiated again subsequently, it will also hit the corresponding cache record in the result set query cache, thereby saving the steps of query execution and obtaining the actual data file from the storage engine, saving the query time and improving the query efficiency.

[0054] The method of the present application is particularly suitable for business scenarios with a large number of concurrent queries and a high number of requests per second (QPS) for database processing. Repetitive calculations for the same statement can be reduced, and when the actual data of the table accessed by the statement does not change, the result set query cache stored in the cache is directly applied, saving the time for accessing the disk and calculation; at the same time, the result set query cache is at the instance level, supporting multiple sessions to access the same result content and supporting the extended query protocol of openGauss; thereby the average response time of the database can be greatly improved.

[0055] It should be noted that the above application scenarios are only shown for the convenience of understanding the present application, and the embodiments of the present application are not limited in this respect. On the contrary, the embodiments of the present application can be applied to any applicable scenario.

[0056] The various non-limiting embodiments of the present application will be described in detail below with reference to the accompanying drawings.

[0057] The following are the explanations of the terms in each of the embodiments:

[0058] openGauss database: an open source relational database management system, released under the Mongolian Lax License v2.

[0059] DDL: full name Data Definition Language, a subset of SQL (Structured Query Language), used to define the structure of a database and its objects, such as tables, views, indexes, and procedures.

[0060] DML: full name Data Manipulation Language, a subset of SQL (Structured Query Language), used to insert, update, and delete data in the database.

[0061] DQL: full name Data Query Language, a subset of SQL (Structured Query Language), used to query data in the database.

[0062] OID: full name Object identifiers, object identifiers in openGauss database, uniquely identifying a database object, which can be a database, table, index, view, tuple, type, etc.

[0063] CSN: short for Commit Sequence Number, used to identify the order of the commit record.

[0064] TUPLE structure: in the implementation in PostgreSQL, each page can contain multiple tuples, and the block saved in the disk is called page in PG, and the block in memory is called buffer, and the row is called tuple.

[0065] Referring to Figure 3 , a query method based on result set query cache in an openGauss database is shown, and the method comprises:

[0066] The query statement is subjected to semantic analysis, and it is judged whether the result set query cache can be enabled according to the type of the query statement.

[0067] When the type of the query statement is DDL or DML, the query statement is determined to be unable to enable the result set query cache; it should be noted that in this embodiment, when the type of the query statement is DDL or DML, it is judged whether it has an influence on the accuracy of the query result, when the query statement of the type DDL or DML has an influence on the accuracy of the query result, the table OID used by the query statement is recorded when the query statement is subjected to semantic analysis, the cache record depending on the table OID in the cache hash table is invalidated during query execution, the query data is obtained from the storage engine and returned to the client initiating the query; when the query statement of the type DDL or DML has no influence on the accuracy of the query result, the query data is obtained from the storage engine and returned to the client initiating the query.

[0068] Since OpenGauss adopts a multi-version-based concurrency control rule, the result set query cache of this embodiment conforms to the visibility rule judgment, so in addition to the above-mentioned three internal methods of concurrent control of new, use and invalid cache, in order to ensure that all tables involved cannot produce new caches during the submission process of a DML\DDL transaction, an additional OID locking table is added.

[0069] The OID locking table records the transaction count of a certain table and the maximum CSN of the transaction that changes the table during the entire database operation period, which is referred to as MaxCSN. The transaction count is increased at the beginning of the transaction submission process and is reduced after the transaction is submitted, and the recording time of the CSN is after the transaction is submitted. In this embodiment, by introducing the OID locking table, it can be judged whether the cache record can be in the effective state by checking whether there is a corresponding table OID in the OID locking table, the transaction count is 0, and the CSN of the current transaction is greater than MaxCSN during the validation phase of the new cache.

[0070] When the type of the query statement is DQL, it is determined whether the query statement satisfies the constraint of the result set query cache, and when the query statement satisfies the constraint of the result set query cache, the query statement is determined as being able to enable the result set query cache.

[0071] As an example of the embodiment, when the type of the query statement is DQL and the query statement does not satisfy the constraint of the result set query cache, query data is obtained from a storage engine and returned to a client initiating the query.

[0072] A hash value of the query statement able to enable the result set query cache is calculated, and it is determined whether the hash value of the query statement hits a cache record in the cache hash table;

[0073] As an example of the embodiment, the query tree key structure of the query statement able to enable the result set query cache is obtained, and the query tree key structure is extracted from the query tree key structure, a character sequence is generated according to the extracted node information, and a hash value of the character sequence is calculated.

[0074] In order to increase the matching efficiency, as shown in the figure, Figure 4 The embodiment divides the cache hash table into a plurality of hash buckets, performs modulo operation on the hash value of the query statement according to the number of the hash buckets, and performs matching on the hash value after the modulo operation in the hash bucket, thereby greatly shortening the linked list of the hash value matching search.

[0075] When the hash value of the query statement matches the cache record in the cache hash table, the hash value of the query statement is determined as hitting the cache record in the cache hash table, and it is verified whether the query statement and the cache record in the hash table belong to the same query by comparing the query tree structure of the query statement with the query tree structure in the cache record in the hash table.

[0076] When the hash value of the query statement does not match the cache record in the cache hash table, the hash value of the query statement is determined as not hitting the cache record in the cache hash table.

[0077] When the hash value of the query statement hits the cache record in the cache hash table, it is determined whether the hit cache record is available at the current time node, and the cache record available at the current time node is returned to the client initiating the query; when the hash value does not hit the cache record in the cache hash table, an initialization cache is newly created synchronously during query execution, and the cache content is filled into the initialization cache, and the effective cache is added into the result set query cache.

[0078] It should be noted that, since the database is a concurrent system, when certain concurrent scenarios exist, it cannot be guaranteed that the hit cache record can be used, therefore, in the method of the embodiment, when the hash value hits the cache record in the cache hash table, it is still further judged whether the current time node can use the hit cache record; when the hit cache record is available at the current time node, the cache execution node is used to convert the cache record into a TUPLE structure recognizable by the query executor, and the cache record is returned to the client initiating the query; when the hit cache record is not available at the current time node, the query data is obtained from the storage engine and returned to the client initiating the query.

[0079] Figure 3 The method described in the application is only a basic embodiment, which can be optimized and expanded, and other optional embodiments of the method can also be obtained.

[0080] Next, another specific embodiment of the query method based on the result set query cache in the openGauss database according to the application will be introduced. Figure 3 Further elaboration is made on the basis of the corresponding embodiment. When the hash value does not hit the cache record in the cache hash table, the initialization cache is newly created synchronously during query execution, and the cache content is filled into the initialization cache, the effective cache is added to the result set query cache, and the following methods are used to achieve the above:

[0081] The cache record is generated by the placeholder operation, and the cache record in the initialization state is held in the cache hash table. As an example, a reference count is set for each cache, the placeholder operation is performed after the query rewriting stage, the hash bucket hit by the query is written by locking the memory, a cache record in the initialization state is generated in the global cache and held by the cache hash table, and the reference count of the cache is updated. Since the placeholder operation in the embodiment is mutually exclusive, multiple same cache records in the initialization state will not appear.

[0082] During the execution of the query statement, the query result of each time is continuously filled into the result set corresponding to the cache record in the initialization state in the form of a tuple, when the query result is filled into the result set, an operation occurs to invalidate the cache, the filling process is terminated, and the context of the query executor gives up holding the cache record.

[0083] As an example, in the continuous filling process, each time the query result is passed to the topmost cache execution node in the form of a tuple, the context of the executor continuously holds the cache record object of the initialization state, and each time the next query result is converted into a space-saving tuple format and appended to the record result set. During the entire process, no lock is added. When a cache invalidating operation occurs during the filling process, the filling process is terminated, and the executor context is abandoned to hold the cache record object.

[0084] When the query statement execution is completed, it is determined again whether a cache invalidating operation occurs during the filling process. When no cache invalidating operation occurs, the state of the filled cache record of the initialization state is updated to be valid.

[0085] In the process of creating a new cache, the embodiment uses the method of "occupying a place first, then filling, and finally validating" to achieve this. Using this method can greatly reduce the time of holding a write lock, thereby minimizing the additional overhead of the logic of building a cache on normal execution of a query statement.

[0086] Figure 3 The embodiment is only a basic embodiment of the method described in the application, and optimization and expansion can be performed on the basis thereof, and other optional embodiments of the method can also be obtained. Next, another specific embodiment of the query method based on a result set query cache in an openGauss database according to the application will be introduced. The embodiment describes the result set query cache invalidation processing in the method of the application.

[0087] In actual application, in order to ensure the efficient application of the result set query cache, part of the cache needs to be invalidated according to different situations.

[0088] As an example, in the method of the embodiment, when some or all records of the cache are not needed, the database administrator can delete them from the cache through a function implemented by a function.

[0089] Specifically, the invalid cache record ID (i.e., CacheID) is specified through a function interface, the corresponding hash value of the query statement is matched in the auxiliary information cache, a certain cache record in the hash table is matched using the hash value and the cache record ID, and the reference count value of the cache record is reduced.

[0090] As another example, in the method of the embodiment, when the data in the cache expires or the table structure changes, causing the result set in the cache to not conform to the actual result that should be queried out, the corresponding cache record is invalidated through an automatic invalidation method.

[0091] Specifically, the automatic invalidation operation of the method of the embodiment is triggered in response to some DDL or DML statement operation that has an impact on the accuracy of query results, when the transaction in which the statements are located is in the process of committing, and before the transaction is actually committed, the cache records related to the OID of the table referenced by all statements in the transaction are invalidated.

[0092] As another example, in the method of the embodiment, when the cache space is insufficient, some data in the cache is automatically deleted to free up cache space.

[0093] Specifically, in the method of the embodiment, before the cache record in the initial state is validated, the size of the space occupied by the corresponding cache record needs to be calculated, and enough cache space to accommodate this cache record is released. The release of cache space is achieved by setting the reference count of the cache to 0. As shown in the following table, when selecting the cache record to be released, all hash buckets of the entire cache are taken as a global LRU sequence, and all elements in each hash bucket are taken as an intra-bucket LRU sequence. When releasing space, the local LRU is combined with the global LRU, which can reduce the granularity of the write lock, so that it can hold a write lock for each bucket, thereby balancing cache utilization and concurrency efficiency. Figure 5

[0094] In the method of the embodiment, the reference count of each cache is the fundamental operation that ensures that a cache record being used will not be released by other threads. All operations or structures of objects that hold a cache record internally will first increase the reference count of the cache record by one. Conversely, when the record is not referenced by an object or structure, the reference count will be reduced by one. When the reference count is 0, the memory space can be truly released, ensuring that a cache record being used will not be invalidated.

[0095] As shown in the following table, in the method of the embodiment, the reference count of each cache is the fundamental operation that ensures that a cache record being used will not be released by other threads. All operations or structures of objects that hold a cache record internally will first increase the reference count of the cache record by one. Conversely, when the record is not referenced by an object or structure, the reference count will be reduced by one. When the reference count is 0, the memory space can be truly released, ensuring that a cache record being used will not be invalidated. Figure 6 The application also provides a device, including a processor 110, a communication interface 120, a memory 130 for storing a processor executable computer program, and a communication bus 140. The processor 110, the communication interface 120, and the memory 130 complete communication with each other through the communication bus 140. The processor 110 realizes the query method based on the result set query cache in the openGauss database by running the executable computer program.

[0096] ​The computer program in the memory 130 can be implemented in the form of a software functional unit and sold or used as an independent product, and can be stored in a computer readable storage medium. Based on this understanding, the technical solutions of the present application essentially or the parts that contribute to the prior art or parts of the technical solutions can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the embodiments of the present application. The aforementioned storage medium includes: a U disk, a mobile hard disk, a read-only memory (ROM, Read-Only Memory), a random access memory (RAM, Random Access Memory), a magnetic disk or an optical disk, and various media that can store program codes.

[0097] The system embodiments described above are only schematic, wherein the units illustrated as separate components can or can not be physically separated, and the components illustrated as units can or can not be physical units, i.e., can be located in one place, or can be distributed on a plurality of network units. Part or all of the modules can be selected based on actual needs to achieve the purpose of the embodiments. Those skilled in the art can understand and implement without creative labor.

[0098] From the above description of the embodiments, those skilled in the art can clearly understand that the embodiments can be implemented by means of software and the necessary universal hardware platform, and of course, can also be implemented by hardware. Based on this understanding, the above technical solutions essentially or the parts that contribute to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., including a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the method of each embodiment or some parts of the embodiment.

[0099] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto, and any skilled person in the art can easily think of changes or replacements within the technical range disclosed by the present application, which should be covered within the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A query method based on result set query caching in the openGauss database, characterized in that, The method includes: After the client initiates a query, it enters the openGauss database server through the protocol layer. After lexical parsing, semantic analysis, query optimization, and query execution, the actual data file content is obtained from the storage engine. The executor passes the results to the protocol layer during the query execution phase, and the protocol layer returns them to the client. A cache record entry is constructed. During the first execution of the query statement, the executor stores the result data one by one into the cache structure, thus constructing the query result set cache for this query statement. Perform semantic analysis on the query statement and determine whether result set query caching can be enabled based on the type of the query statement; Calculate the hash value of the query statement that enables result set query caching, and determine whether the hash value of the query statement hits a cache record in the cache hash table; When the hash value of the query statement matches a cached record in the cache hash table, it is determined whether the matched cached record is available at the current time. If the cached record is available at the current time, it is returned to the client that initiated the query. When the hash value does not match a cached record in the cache hash table, a new initial cache is created synchronously during query execution, and cache content is filled into the initial cache. The effective cache is then added to the result set query cache. Determine whether result set query caching can be enabled based on the type of query statement, including: When the type of the query statement is DDL or DML, the query statement is determined to be unable to enable result set query caching; When the type of the query statement is DQL, determine whether the query statement meets the constraints of the result set query cache. If the query statement meets the constraints of the result set query cache, determine that the query statement can enable the result set query cache. Calculating the hash value of a query statement that enables result set query caching includes: obtaining the key structure of the query tree of the query statement that enables result set query caching, extracting node information of the query tree from the key structure of the query tree, generating a character sequence based on the extracted node information, and calculating the hash value of the character sequence.

2. The query method based on result set query caching in the openGauss database according to claim 1, characterized in that, When the query statement is of type DDL or DML, the query statement is determined to be unable to enable result set query caching, including: Determine whether a query statement of type DDL or DML affects the accuracy of the query results. If a query statement of type DDL or DML affects the accuracy of the query results, record the table OID used by the query statement when performing semantic analysis on the query statement. When the query is executed, invalidate the cached records in the cache hash table that depend on the table OID, retrieve the query data from the storage engine and return it to the client that initiated the query. When a query statement of type DDL or DML has no impact on the accuracy of the query results, the query data is retrieved from the storage engine and returned to the client that initiated the query.

3. The query method based on result set query caching in the openGauss database according to claim 1, characterized in that, Determining whether the hash value of the query statement matches a cached record in the cache hash table includes: The cache hash table is divided into several hash buckets. The hash value of the query statement is moduloed according to the number of hash buckets. The hash values ​​after moduloing are matched within the hash bucket. When the hash value of a query statement matches a cached record in the cache hash table, the hash value of the query statement is determined to have hit a cached record in the cache hash table. When the hash value of a query statement does not match a cached record in the cache hash table, the hash value of the query statement is determined to be a cached record that has not been found in the cache hash table.

4. The query method based on result set query caching in the openGauss database according to claim 3, characterized in that, When the hash value of a query statement matches a cached record in the cache hash table, the hash value of the query statement is determined to have hit a cached record in the cache hash table. This includes: verifying whether the query statement and the cached record in the hash table belong to the same query by comparing whether the query tree structure of the query statement is the same as the query tree structure in the cached record of the hash table.

5. The query method based on result set query caching in the openGauss database according to claim 1, characterized in that, When the hash value of a query matches a cached record in the cache hash table, it is determined whether the matched cached record is available at the current time. If the cached record is available at the current time, it is returned to the client that initiated the query, including: When the cached record that was hit is available at the current time node, the cache execution node is used to convert the cached record into a TUPLE structure that can be recognized by the query executor; When the cached record that was hit is not available at the current time, the query data is retrieved from the storage engine and returned to the client that initiated the query.

6. The query method based on result set query caching in the openGauss database according to claim 1, characterized in that, When the hash value does not match a cached record in the cache hash table, a new initial cache is created synchronously during query execution, and cache content is populated into the initial cache. The effective cache is then added to the result set query cache, including: Cache records are generated through placeholder operations and are stored in the cache hash table in their initial state. During the execution of the query statement, the query results are continuously populated into the result set corresponding to the cached record in the initial state in the format of tuples. When an operation that invalidates the cache occurs during the process of populating the query results into the result set, the population process is terminated and the context of the query executor relinquishes its holding of the cached record. When the query statement is completed, it is checked again whether any operation that would invalidate the cache occurred during the entire filling process. If no operation that would invalidate the cache occurred, the status of the cache record in the initial state after filling is updated to be effective.

7. The query method based on result set query caching in the openGauss database according to claim 6, characterized in that, The process involves generating a cache record through a placeholder operation and holding the cache record in an initialized state in the cache hash table. This includes setting a reference count for each cache, performing a placeholder operation after the query rewrite phase, copying the hash bucket hit by the query to memory by acquiring a write lock, generating a cache record in the global cache that is held by the cache hash table in an initialized state, and updating the reference count of the cache.

8. The query method based on result set query caching in the openGauss database according to claim 6, characterized in that, A cache record in an active state includes key cache information, which includes the cache record ID, the complete query tree structure, the query tree hash value, and the result set stored in tuple form.

9. The query method based on result set query caching in the openGauss database according to claim 1, characterized in that, The method also includes invalidating the result set query cache.

10. The query method based on result set query caching in the openGauss database according to claim 9, characterized in that, Invalidate the result set query cache, including: When one or all cached records are no longer needed, the invalidated cached record ID is specified through the function interface. The corresponding query statement hash value is matched in the auxiliary information cache. The hash value and the cached record ID are used to match a cached record in the hash table and its reference count is reduced. The record is then removed from the cache by manually invalidating it. When data in the cache expires or the table structure changes, causing the result set in the cache to no longer match the actual query result, an automatic invalidation operation is triggered when responding to certain DDL or DML statement operations that affect the accuracy of the query result. When the transaction in which the DDL or DML statement is located is in the process of committing, and before the transaction is actually committed, the cache records involved in the OID of the table referenced by all statements in the transaction are invalidated. When the cache space is insufficient, the corresponding data in the cache is automatically deleted by setting the cache reference count to the initial value.

11. A computer device, characterized in that, The computer device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the program, implements the steps of the method according to any one of claims 1-10.

Images