LOB database data query methods, devices, equipment and media

Abstract

This invention discloses a method, apparatus, device, and medium for querying data in a Logical Object (LOB) database, comprising: determining, based on a received database query request, whether the out-of-row vector data of the LOB database participates in the corresponding logical operation; if the out-of-row vector data does not participate in the logical operation, then reading the corresponding in-row business data based on the query request to generate a query response result; if the out-of-row vector data participates in the logical operation, then determining the corresponding LOB locator; querying a preset cache area to determine whether there is a memory storage address associated with the LOB locator; if there is, then reading the out-of-row vector data in the preset memory area based on the memory storage address when participating in the logical operation; if there is not, then reading the out-of-row vector data based on the disk storage address when participating in the logical operation; and generating a response result based on the out-of-row vector data. This method reduces the number of disk accesses for data queries and improves concurrent processing capabilities.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, device, and medium for querying data in a LOB database. Background Technology

[0002] With the development of artificial intelligence technology, the importance of vector data is becoming increasingly prominent (e.g., in recommendation systems, image search). Efficient management of vector data has become a crucial database metric. Vector data generated in production scenarios can reach thousands or even tens of thousands of dimensions. Storing such vectors often exceeds the storage threshold of a single column or page, requiring extended storage, such as Large Objects (LOBs). In complex queries, the same vector is often read from disk multiple times, leading to low query efficiency. Summary of the Invention

[0003] This invention provides a method, apparatus, device, and medium for querying data in a LOB database, to solve the problem of low query efficiency caused by repeated disk access when reading vector data multiple times in related technologies.

[0004] In a first aspect, the present invention provides a method for querying data in a LOB database, comprising: Based on the received database query request, determine whether the row out-of-row vector data of the LOB database participates in the corresponding logical operation, and obtain the logical judgment result; If the logical judgment result is that the out-of-row vector data does not participate in the logical operation, then based on the database query request, the corresponding in-row business data is read, and based on the in-row business data, the response result of the database query request is generated; If the logical judgment result indicates that the out-of-row vector data participates in the logical operation, then the LOB locator corresponding to the out-of-row vector data is determined. The LOB locator is used to indicate the disk storage address of the out-of-row vector data. Query the preset cache area to determine whether there is a memory storage address associated with the LOB locator in the preset cache area. The memory storage address is used to indicate the storage location of the out-of-row vector data in the preset memory area. If a memory storage address exists in the preset cache area, then when participating in logical operations, the out-of-row vector data in the preset memory area is read based on the memory storage address; If there is no memory storage address in the preset cache area, when participating in logical operations, the out-of-row vector data is read based on the disk storage address, the out-of-row vector data is stored in the preset memory area, and the memory storage address and LOB locator are associated and stored in the preset cache area. Based on the read out-of-row vector data, generate the response results of the database query request.

[0005] In some embodiments, based on the received database query request, it is determined whether the row out-of-row vector data of the LOB database participates in the corresponding logical operation, and the logical judgment result is obtained, including: Based on the database query request, determine the query target and query logic; Based on the query target and query logic, determine whether the out-of-row vector data participates in the corresponding logical operation, and obtain the logical judgment result.

[0006] In some embodiments, based on a database query request, the corresponding intra-line business data is read, including: Based on the database query request, determine whether there is in-line business data in the page cache area of ​​the LOB database; If it is determined that inline business data exists in the page cache area, then the inline business data is read from the page cache area; If it is determined that there is no in-line business data in the page cache area, the in-line business data is read from the corresponding data page in the LOB database and stored in the page cache area.

[0007] In some embodiments, after determining the query target and query logic based on the database query request, the method further includes: Based on the query logic, determine whether to materialize the intermediate query results generated by the database query request, and obtain the materialization judgment result. If the materialization judgment result is to perform materialization processing, then the LOB locator corresponding to the out-of-row vector data and the in-row business data will be materialized to the preset materialization area.

[0008] In some embodiments, associating and storing the memory storage address and LOB locator in a preset cache area includes: The LOB locator is determined as the index key, and the memory storage address is determined as the index value; The memory storage address and LOB locator are stored in the preset cache area in the form of key-value pairs.

[0009] In some embodiments, before generating the response result of the database query request based on the read out-of-row vector data, the method further includes: Get the maximum cache size corresponding to the out-of-row vector data, and count the current cache size of the out-of-row vector data; When the current cache count reaches the maximum cache count, clear the memory storage addresses and LOB locators in the preset cache area, clear the corresponding out-of-row vector data in the preset memory area, and reset the current cache count.

[0010] In some embodiments, before obtaining the maximum cache size corresponding to the out-of-row vector data, the method further includes: Get the available memory space of the preset memory region, the data space occupied by the out-of-row vector data, and the available cache space of the preset cache region; Based on the data occupancy space and available memory space, determine the maximum data storage capacity of the preset memory area; Based on the available cache space and the maximum data storage capacity, determine the maximum number of caches corresponding to the out-of-row vector data.

[0011] Secondly, the present invention provides a LOB database data query device, comprising: The logic judgment module is used to determine whether the out-of-row vector data of the LOB database participates in the corresponding logical operation based on the received database query request, and to obtain the logical judgment result. The first response module is used to read the corresponding in-row business data based on the database query request if the logical judgment result is that the out-of-row vector data does not participate in the logical operation, and to generate the response result of the database query request based on the in-row business data. The locator determination module is used to determine the LOB locator corresponding to the out-of-row vector data if the logical judgment result is that the out-of-row vector data participates in the logical operation. The LOB locator is used to indicate the disk storage address of the out-of-row vector data. The address query module is used to query the preset cache area and determine whether there is a memory storage address associated with the LOB locator in the preset cache area. The memory storage address is used to indicate the storage location of the out-of-row vector data in the preset memory area. The first reading module is used to read the out-of-row vector data in the preset memory area based on the memory storage address when participating in logical operations if a memory storage address exists in the preset cache area. The second reading module is used to read the out-of-row vector data based on the disk storage address when participating in logical operations if there is no memory storage address in the preset cache area, store the out-of-row vector data in the preset memory area, and associate the memory storage address and LOB locator in the preset cache area. The second response module is used to generate the response results of the database query request based on the read out-of-row vector data.

[0012] Thirdly, the present invention provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the above-described LOB database data query method.

[0013] Fourthly, the present invention provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described LOB database data query method.

[0014] In the above-mentioned solution implemented by the LOB database data query method, apparatus, device, and medium, the method includes: based on the received database query request, determining whether the out-of-row vector data of the LOB database participates in the corresponding logical operation, and obtaining a logical judgment result; if the logical judgment result is that the out-of-row vector data does not participate in the logical operation, then based on the database query request, reading the corresponding in-row business data, and generating a response result for the database query request based on the in-row business data; if the logical judgment result is that the out-of-row vector data participates in the logical operation, then determining the LOB locator corresponding to the out-of-row vector data, the LOB locator being used to indicate the disk storage address of the out-of-row vector data; querying a preset cache area, confirming... The system determines whether a memory storage address associated with the LOB locator exists in the preset cache region. This memory storage address indicates the storage location of the out-of-row vector data within the preset memory region. If a memory storage address exists in the preset cache region, the out-of-row vector data is read from the preset memory region based on the memory storage address during logical operations. If no memory storage address exists in the preset cache region, the out-of-row vector data is read from the disk storage address during logical operations, stored in the preset memory region, and associated with the memory storage address and the LOB locator in the preset cache region. Based on the read out-of-row vector data, the system generates the response result for the database query request. This method improves data query efficiency by determining whether out-of-row data must participate in logical operations, thus avoiding reading large volumes of out-of-row vector data in unnecessary scenarios. By caching accessed out-of-row vector data in a preset memory area and associating this memory address with the LOB locator in the preset cache area, subsequent accesses to the same out-of-row vector data can be quickly retrieved from the preset memory area directly based on the cached memory address, helping to reduce disk accesses and improve data query efficiency. Furthermore, by only reading out-of-row vector data when it is actually involved in logical operations, it avoids memory waste caused by preloading and improves the concurrent processing capability of data queries. Attached Figure Description

[0015] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the description of the embodiments of the present invention 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.

[0016] Figure 1 This is an example diagram illustrating an application scenario of the LOB database data query method in one embodiment of the present invention; Figure 2 This is a flowchart of a method for querying data in an LOB database according to an embodiment of the present invention; Figure 3This is a detailed flowchart of step S101 of the LOB database data query method in one embodiment of the present invention; Figure 4 This is a detailed flowchart of step S106 of the LOB database data query method in one embodiment of the present invention; Figure 5 This is a flowchart of step S107 of the LOB database data query method in one embodiment of the present invention; Figure 6 This is a schematic diagram of a LOB database data query device according to an embodiment of the present invention; Figure 7 This is a schematic block diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0017] The LOB database data query method provided in this embodiment of the invention can be applied to, for example... Figure 1 The application environment shown includes a client and a server. The client communicates with the server over a network and can interact with the model deployed on the server that executes the LOB database data query method. The client, also known as the user terminal, refers to the program that provides local services to the client, corresponding to the server. The client can be installed on, but is not limited to, various personal computers, laptops, smartphones, tablets, and portable wearable devices. The server can be implemented using a standalone server or a server cluster consisting of multiple servers. This server can receive data access requests sent by the client and return processed response results to the client.

[0018] In one embodiment, such as Figure 2 As shown, a method for querying data in a LOB database is provided, with an example of its application on a server. The method includes the following steps: S101, based on the received database query request, determine whether the row out-of-row vector data of the LOB database participates in the corresponding logical operation, and obtain the logical judgment result; S102, if the logical judgment result is that the out-of-row vector data does not participate in the logical operation, then based on the database query request, the corresponding in-row business data is read, and based on the in-row business data, the response result of the database query request is generated; S103, if the logical judgment result is that the out-of-row vector data participates in the logical operation, then determine the LOB locator corresponding to the out-of-row vector data; S104, query the preset cache area to determine whether there is a memory storage address associated with the LOB locator in the preset cache area. The memory storage address is used to indicate the storage location of the out-of-row vector data in the preset memory area. S105, if a memory storage address exists in the preset cache area, then when participating in logical operations, the out-of-row vector data in the preset memory area is read based on the memory storage address; S106 If there is no memory storage address in the preset cache area, when participating in logical operations, the off-row vector data is read based on the disk storage address, the off-row vector data is stored in the preset memory area, and the memory storage address and LOB locator are associated and stored in the preset cache area. S107, Based on the read out-of-row vector data, generate the response result of the database query request.

[0019] As an example, in step S101, after the server receives the database query request sent by the client, it performs syntax parsing and semantic analysis on the database query request to determine the request target of the database query request, and then determines whether the high-dimensional vector data stored outside the row (i.e., the out-of-row vector data) needs to participate in any logical operation of this query, and obtains the logical judgment result.

[0020] The logical operations can include filtering, calculation, projection, type conversion, etc. The out-of-row vector data is a high-dimensional vector stored in the extended storage disk of the LOB database. The LOB database is a database that adopts the LOB extended storage mechanism.

[0021] For example, taking a subquery in a database query request that involves sorting the database data identifier field as an example, in this case, the subquery does not involve specific out-of-row vector data, so the logical judgment result is that the out-of-row vector data does not participate in the logical operation; if the database query request involves the projection of the vector field and the projection of the vector distance calculation result, that is, it involves specific out-of-row vector data, then the logical judgment result is that the out-of-row vector data participates in the logical operation.

[0022] As an example, in step S102, when it is determined that the out-of-row vector data does not participate in logical operations, only the ordinary business fields (i.e., in-row business data) within the table rows of the LOB database are read. No disk read operations on the out-of-row vector data are triggered, and the query logic is directly completed and the response result is generated based on the read in-row business data. This avoids redundant loading and memory consumption of high-dimensional vector data.

[0023] This includes internal business data such as data identity ID, data category, and creation timestamp.

[0024] For example, taking a subquery involving sorting the database id field in a database query request, in this case, it is only necessary to read the inline values ​​of the id field to participate in the sorting, and generate the response result based on the sorting result.

[0025] In one embodiment, due to the characteristics of LOB databases, in-row business data and their corresponding LOB locators always coexist in the same data row. Therefore, while reading in-row business data, the corresponding LOB locator can be read to reserve an access path for possible subsequent vector reading. It should be understood that at this time, only the LOB locator is read without triggering disk loading, that is, the corresponding out-of-row vector data is not read.

[0026] As an example, in step S103, when it is determined that the out-of-row vector data participates in logical operations, the server locates the LOB locator associated with the out-of-row vector data from the currently processed data row of the database table.

[0027] The LOB locator is used to indicate the physical storage location of the out-of-row vector data in a preset disk storage area. Optionally, the LOB locator is a LOB locator stored in a database table row, but it is not limited to this and can also be other forms of address pointers or index identifiers.

[0028] It should be understood that out-of-row vector data is data stored outside the rows of the LOB database, while LOB locators are structured data within the rows of the database table.

[0029] In one embodiment, the vector field name contained in the database query request can be used as a keyword to retrieve the corresponding mapping table and determine the LOB locator corresponding to the vector field name from multiple data locators in the database table row; however, it is not limited to this and the LOB locator can also be determined by other means.

[0030] As an example, in step S104, the LOB locator can be identified as an index, and a matching query can be performed on the preset cache area. By determining whether there is an associated mapping relationship that includes the LOB locator, it can be determined whether there is a memory storage address associated with the LOB locator in the preset cache area.

[0031] The preset cache area is a separate cache area set up by the database for high-dimensional vector data. It is different from the page cache shared by ordinary table data in the database. It is only used to store the key-value pair mapping relationship between LOB locators and corresponding memory storage addresses, so as to avoid the problem of hot data being eliminated due to vector data and ordinary business data competing for cache resources.

[0032] The memory storage address is used to indicate the specific storage location of the out-of-row vector data in the preset memory area.

[0033] As an example, in step S105, when it is confirmed that there is a memory storage address associated with the LOB locator in the preset cache area, when the out-of-row vector data participates in logical operations, the corresponding out-of-row vector data can be directly read from the preset memory area through the memory storage address without having to access the preset disk storage area (such as the out-of-row LOB extension area) again. This avoids the same vector data being repeatedly read from the disk in multiple rounds of processing in a single query, thereby reducing disk I / O overhead from the root, improving query execution efficiency, and ensuring that out-of-row vector data is only loaded when participating in logical operations, avoiding redundant overhead from preloading.

[0034] The preset memory area is a memory space allocated by the database for the current query task to temporarily cache high-dimensional vector data. It is physically or logically isolated from the database's general business processing memory and is only used to store the out-of-row vector data read during this query, ensuring the efficiency of vector data reading and the accuracy of memory management.

[0035] The memory storage address is used to indicate the storage location of the out-of-row vector data in a preset memory region.

[0036] For example, for complex queries that include sorting subqueries and vector distance calculations, the out-of-row vector data is not read during the execution of the sorting subquery. Only when the sorting is completed and the vector distance calculation is entered, the cached out-of-row vector data is read from the preset memory area through the memory storage address.

[0037] As an example, in step S106, when it is confirmed that there is no memory storage address associated with the LOB locator in the preset cache area, the data can be read from the preset disk storage area through the disk storage address indicated by the LOB locator only when the out-of-row vector data participates in logical operations; then the read out-of-row vector data is written to the preset memory area, and the memory storage address corresponding to the vector data in the preset memory area is obtained; finally, the association mapping relationship between the LOB locator and the memory storage address is stored in the preset cache area to facilitate subsequent re-call of the out-of-row vector data.

[0038] The disk storage address is the physical storage address of the out-of-row vector data in a preset disk storage area (such as the out-of-row LOB extended storage area), which is uniquely indicated by the LOB locator. This ensures that the disk read operation of the out-of-row vector data is triggered only when it is actually called for the first time, rather than being read in advance during the table scan phase, thus avoiding invalid disk I / O operations. At the same time, by mapping the association between the LOB locator and the memory storage address to the preset cache area, it is ensured that all subsequent calls to the out-of-row vector data can be read directly from memory.

[0039] For example, taking the projection of the embedding field in a database query request as the first call scenario for the out-of-row vector, if there is no corresponding mapping relationship in the preset cache area, the server reads the out-of-row vector data through the disk address indicated by the LOB locator, writes it into the preset memory area, binds the memory storage address of the out-of-row vector data with the LOB locator and stores it in the preset cache area for direct use in subsequent distance calculation stages.

[0040] As an example, in step S107, based on the read out-of-row vector data, the full operation execution logic corresponding to the database query request can be completed, the processing result can be encapsulated into a standardized query result according to a preset format, the response result corresponding to the database query request can be generated, and the response result can be returned to the client that initiated the request.

[0041] For example, taking a database query request that includes vector similarity retrieval as an example, the server calculates the cosine distance between the retrieved out-of-row vector data and the retrieved vector, filters out matching data within the distance threshold, sorts them in ascending order of distance and encapsulates them into a result set, and generates the corresponding query response result to return to the client.

[0042] In summary, this invention proposes a method for querying data in a LOB database, comprising: based on a received database query request, determining whether the out-of-row vector data in the LOB database participates in the corresponding logical operation, and obtaining a logical judgment result; if the logical judgment result indicates that the out-of-row vector data does not participate in the logical operation, then based on the database query request, reading the corresponding in-row business data, and generating a response result for the database query request based on the in-row business data; if the logical judgment result indicates that the out-of-row vector data participates in the logical operation, then determining the LOB locator corresponding to the out-of-row vector data, the LOB locator being used to indicate the disk storage address of the out-of-row vector data; querying a preset cache area to determine the preset cache. Does the region contain a memory storage address associated with the LOB locator? The memory storage address indicates the storage location of the out-of-row vector data in the preset memory region. If the memory storage address exists in the preset cache region, the out-of-row vector data in the preset memory region is read based on the memory storage address when participating in logical operations. If the memory storage address does not exist in the preset cache region, the out-of-row vector data is read based on the disk storage address when participating in logical operations, the out-of-row vector data is stored in the preset memory region, and the memory storage address and LOB locator are associated and stored in the preset cache region. Based on the read out-of-row vector data, the response result of the database query request is generated. This method improves data query efficiency by determining whether out-of-row data must participate in logical operations, thus avoiding reading large volumes of out-of-row vector data in unnecessary scenarios. By caching accessed out-of-row vector data in a preset memory area and associating this memory address with the LOB locator in the preset cache area, subsequent accesses to the same out-of-row vector data can be quickly retrieved from the preset memory area directly based on the cached memory address, helping to reduce disk accesses and improve data query efficiency. Furthermore, by only reading out-of-row vector data when it is actually involved in logical operations, it avoids memory waste caused by preloading and improves the concurrent processing capability of data queries.

[0043] In one embodiment, such as Figure 3 As shown, step S101, which involves determining whether the out-of-row vector data of the LOB database participates in the corresponding logical operation based on the received database query request, and obtaining the logical judgment result, includes: S201, Based on the database query request, determine the query target and query logic; S202, based on the query target and query logic, determine whether the out-of-row vector data participates in the corresponding logical operation, and obtain the logical judgment result.

[0044] As an example, in step S201, after the server receives the database query request sent by the client, it first performs lexical analysis, syntax parsing and semantic verification on the request to generate an executable query execution plan within the LOB database; then, based on the query execution plan, it extracts the query target and query logic for this query.

[0045] The query target refers to the final data content to be obtained in this query, including the set of fields to be returned, data filtering conditions, result sorting rules, etc.; the query logic refers to the execution flow and data processing rules of this query, including the execution order of query steps, the types of operators involved in each step, data flow paths, etc.

[0046] For example, after parsing the query request, it is determined that the query target is to obtain the id field value of the data row, and the cosine distance value between the embedding vector of that row and the specified vector; the query logic is to first execute a subquery to read the id and embedding fields from the table and sort them in ascending order by id, then perform cosine distance calculation based on the sorted results, and finally return the calculation results.

[0047] As an example, in step S202, all operators and field references in the query execution plan are traversed, and each query step is checked one by one to see if it involves reading, calculating, transforming or outputting out-of-row vector data. If any query step involves the above operations, it is determined that the out-of-row vector data participates in the logical operation of this query. If none of the query steps involve the above operations, it is determined that the out-of-row vector data does not participate in the logical operation of this query.

[0048] For example, analyzing a query request reveals that its query target only involves the in-row id, creation time, and data category fields, and the query logic only includes data filtering and pagination operations, without involving any operations related to out-of-row vector data. Therefore, it is determined that out-of-row vector data does not participate in logical operations.

[0049] For the aforementioned query request that includes cosine distance calculation, its query target includes the vector distance calculation result, and the query logic includes reading the vector field and cosine distance calculation operation. Therefore, it is determined that the out-of-row vector data participates in the logical operation.

[0050] In one optional embodiment, based on the query logic, it can be determined whether to materialize the intermediate query results generated by the database query request to obtain a materialization judgment result; if the materialization judgment result is to materialize, then the LOB locator corresponding to the out-of-row vector data and the in-row business data are materialized to the preset materialization area.

[0051] Specifically, after determining the query target and query logic based on the database query request, the server traverses all logical operation nodes in the generated query execution plan, determining whether the execution of each logical operation node requires temporary storage of intermediate calculation results, multiple rounds of data transfer, or cross-operation data sharing. If any of these requirements exist, it is determined that the intermediate query results need to be materialized, and the corresponding materialization judgment result is obtained. When materialization is required, only the in-row business data and the corresponding LOB locator contained in the current intermediate query results are written to the preset materialization area, without needing to read and write the out-of-row vector data ontology.

[0052] For example, when the query logic includes operations such as sorting, grouping, deduplication, pagination, or nested subqueries, the database needs to temporarily store intermediate processing results in the materialized area for subsequent steps. In this case, the materialized area only stores in-row business data such as id, category_id, and create_time, as well as a LOB locator corresponding to each row of data; it does not contain any out-of-row vector data.

[0053] It should be understood that intermediate operations such as sorting, grouping, and nested subqueries aim to rearrange, aggregate, or transform data rows. Their execution relies solely on the numerical values ​​of the intra-row business data and does not require access to the content of the extra-row vector data. The LOB locator, as the unique identifier for the extra-row vector data, only needs to complete the transformation and temporary storage along with the intra-row business data to ensure that subsequent steps can accurately locate the corresponding extra-row vector data when needed.

[0054] In one embodiment, step S102, which is to read the corresponding intra-line business data based on the database query request, may include: determining whether intra-line business data exists in the page cache area of ​​the LOB database based on the database query request; if intra-line business data exists in the page cache area, then reading the intra-line business data from the page cache area; if intra-line business data does not exist in the page cache area, then reading the intra-line business data from the data page corresponding to the LOB database and storing the intra-line business data in the page cache area.

[0055] Specifically, the server can determine the database table, data row range, and corresponding business fields within the row that need to be accessed based on the database query request; then, based on the physical storage location of the data rows, it can locate the database data page where these data rows are located; finally, it can check whether these data pages have been loaded in the page cache area to determine whether the business data within the row exists in the page cache area.

[0056] The page cache area is a general-purpose memory cache area of ​​the LOB database system, used to temporarily store table data pages read from the disk, thereby reducing disk I / O operations by utilizing the high-speed read and write characteristics of memory.

[0057] For example, for a query request that needs to retrieve the identification information and creation time of data rows within a specified ID range, the server first determines that it needs to access the data rows with IDs from 1000 to 2000 in the vtable table, for example, these data rows are distributed in data pages 5 to 8; then it checks whether there are cached copies of data pages 5 to 8 in the page cache area.

[0058] Furthermore, once it is confirmed that the required data page has been loaded into the page cache area, the server directly retrieves the corresponding in-line business data from the cached data page in memory without accessing the disk.

[0059] For example, if data pages 5 through 8 have been recently accessed and are still in the page cache area, the server directly reads the data row identifier and creation time field values ​​from these data pages in the page cache area.

[0060] When it is confirmed that the required data page has not been loaded into the page cache area, the server initiates an IO request to read the in-line business data from the corresponding data page; after the reading is completed, it is loaded into the page cache area.

[0061] For example, if data pages 5 through 8 are not in the page cache area, the server reads the data row identifier and creation time field values ​​from these four data pages and stores them in the page cache area. Subsequent accesses to any data row in these data pages can then directly retrieve data from the cache.

[0062] In one embodiment, such as Figure 4 As shown, step S106, which associates the memory storage address and LOB locator with the preset cache area, includes: S301, the LOB locator is determined as the index key, and the memory storage address is determined as the index value; S302 stores the memory storage address and LOB locator in a preset cache area in the form of key-value pairs.

[0063] As an example, in step S301, after the out-of-row vector data is completely written into the preset memory area, the LOB locator corresponding to the out-of-row vector data can be determined as the index key, and the memory storage address corresponding to the out-of-row vector data in the preset memory area can be obtained, and the memory storage address can be determined as the index value that matches the aforementioned index key.

[0064] As an example, in step S302, a corresponding association mapping relationship can be constructed based on the determined index key and index value. This association mapping relationship is stored in a preset cache area in the form of key-value pairs, thus completing the caching of the out-of-row vector data. This ensures that subsequent calls to the same out-of-row vector data can directly retrieve the corresponding memory storage address through the LOB locator without needing to access the disk again.

[0065] In one embodiment, multiple sets of key-value pairs corresponding to multiple vector fields within the same data row can be aggregated and stored in units of row number. This facilitates the batch clearing of all cache mapping relationships corresponding to the same data row during subsequent data management of the preset cache area, thereby improving the execution efficiency of memory release. Alternatively, various storage media such as distributed cache and hash table can be used to store key-value pairs, and this invention does not limit the use of such storage media.

[0066] In one embodiment, such as Figure 5 As shown, before step S107, which generates the response result of the database query request based on the read out-of-row vector data, the following steps are also included: S401, obtain the maximum number of cached data corresponding to the out-of-row vector data, and count the current number of cached data of the out-of-row vector data in the preset cache area; S402, when the current cache count reaches the maximum cache count, clear the memory storage addresses and LOB locators in the preset cache area, clear the corresponding out-of-row vector data in the preset memory area, and reset the current cache count.

[0067] As an example, in step S401, during the execution of the current database query request, the maximum number of cached out-of-row vector data corresponding to the current query task can be obtained first. At the same time, based on the execution progress of the current query, the current number of cached out-of-row vector data that has been read from the cache and whose corresponding cache mapping relationship and memory space have not yet been released can be counted in real time.

[0068] Among them, the maximum cache number refers to the maximum number of data rows that are allowed to be cached in the preset memory area at the same time in this query task. It is a threshold for controlling the memory peak during the query process, and its value can be flexibly configured according to the available memory of the database, the volume of a single vector data, and the query execution model. The current cache count refers to the total number of valid data rows corresponding to the out-of-row vector data that has been read and cached in the preset memory area and has not yet been released from the cache. It can correspond to the database query model.

[0069] In one embodiment, the maximum cache size can be calculated by obtaining the current available storage space of a preset memory region and the preset unit storage space occupancy value of a single row out-of-row vector data; alternatively, the maximum cache size can be determined directly based on the fixed threshold parameters pre-configured in the current cache database system.

[0070] For example, the available memory space of a preset memory region, the data space occupied by out-of-row vector data, and the available cache space of a preset cache region can be obtained; based on the data space occupied and the available memory space, the maximum data storage volume of the preset memory region can be determined; based on the available cache space and the maximum data storage volume, the maximum number of caches corresponding to the out-of-row vector data can be determined.

[0071] That is, by dividing the available memory space by the unit storage space occupied by a single row of out-of-row vector data, the maximum number of data rows that the preset memory area can accommodate is obtained; then, based on the available cache space of the preset cache area, the upper limit of the number of key-value pairs that can be stored is calculated; finally, by comparing these two values, the maximum number of out-of-row vector data cached is determined (for example, the smaller of the two values ​​is taken as the maximum number of out-of-row vector data cached for this query task).

[0072] As an example, in step S402, the current cache count obtained by statistics can be compared with the preset maximum cache count in real time. When the current cache count reaches the maximum cache count, the association mapping relationship between the LOB locator and the memory storage address of the corresponding processed data row in the preset cache area is cleared according to the order in which the data rows are processed. The storage space of the corresponding row out-of-line vector data in the preset memory area is released synchronously, and the statistical value of the current cache count is reset after the cache is released.

[0073] The cache clearing operation only targets data rows for which out-of-row vector data reading has been completed, and always retains the cache mapping relationship and vector data of the data row currently being processed, thereby ensuring that the out-of-row vector data of the current row only needs to perform one disk read operation during multiple calls; the current cache number reset rule can be flexibly adjusted according to the value of the maximum cache number to ensure that it matches the actual amount of data retained after the cache is released.

[0074] For example, taking the query using the Volcano database execution model as an example, the maximum cache count corresponding to the out-of-row vector data is set to 1, meaning that only the out-of-row vector data corresponding to one row of data is allowed to be cached at the same time. The server executes the query operation sequentially according to the data row granularity based on the Volcano model: each time a row of data is obtained through the iteration interface of the Volcano model, after the disk read and cache write operations are completed when the out-of-row vector data of that row is called for the first time, the effective current cache count corresponding to the cached and unreleased out-of-row vector data is synchronously accumulated to 1, which is exactly the preset maximum cache count; when the query processing of the current data row is completed and before calling the iteration interface to obtain the next row of data, the key-value pair mapping relationship between the LOB locator of the current row and the memory storage address of the current row in the preset cache area is cleared, the out-of-row vector data of the current row in the preset memory area is released synchronously, and the effective current cache count is reset to 0; this is executed in a loop, ensuring that the out-of-row vector data of the same row is read from the disk at most once in a single query, thereby solving the core problems of excessive memory consumption caused by caching the full out-of-row vector data and the eviction of ordinary business hot data by caching.

[0075] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0076] In one embodiment, a LOB database data query device is provided, which corresponds one-to-one with the LOB database data query method described in the above embodiments. For example... Figure 6 As shown, the LOB database data query device includes a logic judgment module 501, a first response module 502, a locator determination module 503, an address query module 504, a first reading module 505, a second reading module 506, and a second response module 507. Detailed descriptions of each functional module are as follows: The logic judgment module 501 is used to determine whether the out-of-row vector data of the LOB database participates in the corresponding logical operation based on the received database query request, and to obtain the logic judgment result. The first response module 502 is used to read the corresponding in-row business data based on the database query request if the logical judgment result is that the out-of-row vector data does not participate in the logical operation, and to generate the response result of the database query request based on the in-row business data. The locator determination module 503 is used to determine the LOB locator corresponding to the out-of-row vector data if the logical judgment result is that the out-of-row vector data participates in the logical operation. The LOB locator is used to indicate the disk storage address of the out-of-row vector data. Address query module 504 is used to query the preset cache area to determine whether there is a memory storage address associated with the LOB locator in the preset cache area. The memory storage address is used to indicate the storage location of the out-of-row vector data in the preset memory area. The first reading module 505 is used to read the out-of-row vector data in the preset memory area based on the memory storage address when participating in logical operations if a memory storage address exists in the preset cache area. The second reading module 506 is used to read the out-of-row vector data based on the disk storage address when participating in logical operations if there is no memory storage address in the preset cache area, store the out-of-row vector data in the preset memory area, and associate the memory storage address and LOB locator in the preset cache area. The second response module 507 is used to generate a response result for the database query request based on the read out-of-row vector data.

[0077] In one embodiment, the logic judgment module 501 is further configured to determine the query target and query logic based on the database query request; Based on the query target and query logic, determine whether the out-of-row vector data participates in the corresponding logical operation, and obtain the logical judgment result.

[0078] In one embodiment, the first response module 502 is further configured to determine, based on the database query request, whether there is in-line business data in the page cache area of ​​the LOB database; If it is determined that inline business data exists in the page cache area, then the inline business data is read from the page cache area; If it is determined that there is no in-line business data in the page cache area, the in-line business data is read from the corresponding data page in the LOB database and stored in the page cache area.

[0079] In one embodiment, the logic judgment module 501 is further configured to, based on the query logic, determine whether to materialize the intermediate query results generated by the database query request, and obtain a materialization judgment result; If the materialization judgment result is to perform materialization processing, then the LOB locator corresponding to the out-of-row vector data and the in-row business data will be materialized to the preset materialization area.

[0080] In one embodiment, the second reading module 506 is further configured to determine the LOB locator as the index key and the memory storage address as the index value; The memory storage address and LOB locator are stored in the preset cache area in the form of key-value pairs.

[0081] In one embodiment, the second response module 507 is further configured to obtain the maximum cache number corresponding to the out-of-row vector data and count the current cache number of the out-of-row vector data; When the current cache count reaches the maximum cache count, clear the memory storage addresses and LOB locators in the preset cache area, clear the corresponding out-of-row vector data in the preset memory area, and reset the current cache count.

[0082] In one embodiment, the second response module 507 is further configured to obtain the available memory space of a preset memory region, the data occupied space of the out-of-row vector data, and the available cache space of a preset cache region; Based on the data occupancy space and available memory space, determine the maximum data storage capacity of the preset memory area; Based on the available cache space and the maximum data storage capacity, determine the maximum number of caches corresponding to the out-of-row vector data.

[0083] This invention provides a LOB database data query device, comprising: a logic judgment module, used to determine whether out-of-row vector data of the LOB database participates in the corresponding logical operation based on a received database query request, and obtain a logic judgment result; a first response module, used to read the corresponding in-row business data based on the database query request and generate a response result for the database query request based on the in-row business data if the logic judgment result indicates that the out-of-row vector data does not participate in the logical operation; a locator determination module, used to determine the LOB locator corresponding to the out-of-row vector data if the logic judgment result indicates that the out-of-row vector data participates in the logical operation, the LOB locator indicating the disk storage address of the out-of-row vector data; and an address query module, used to query a preset cache area to determine... The system checks whether a memory storage address associated with the LOB locator exists in the preset cache region. The memory storage address is used to indicate the storage location of the out-of-row vector data in the preset memory region. The first reading module is used to read the out-of-row vector data in the preset memory region based on the memory storage address when participating in logical operations if a memory storage address exists in the preset cache region. The second reading module is used to read the out-of-row vector data based on the disk storage address when participating in logical operations if a memory storage address does not exist in the preset cache region, store the out-of-row vector data in the preset memory region, and associate the memory storage address and the LOB locator in the preset cache region. The second response module is used to generate a response result for the database query request based on the read out-of-row vector data. This device improves data query efficiency by determining whether out-of-row data must participate in logical operations, thus avoiding reading large volumes of out-of-row vector data in unnecessary scenarios. By caching accessed out-of-row vector data in a preset memory area and associating this memory address with the LOB locator in the preset cache area, subsequent accesses to the same out-of-row vector data can be quickly retrieved from the preset memory area directly based on the cached memory address, helping to reduce disk accesses and improve data query efficiency. Furthermore, by only reading out-of-row vector data when it is actually involved in logical operations, it avoids memory waste caused by preloading and improves the concurrent processing capability of data queries.

[0084] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 7As shown, the computer device includes a processor, memory, network interface, and database connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and database. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The database is used for data retrieved in the LOB database query method. The network interface is used for communication with external terminals via a network connection. When executed by the processor, the computer program implements an LOB database query method.

[0085] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement a LOB database data query method.

[0086] In one embodiment, a computer-readable storage medium is provided, the computer-readable storage medium storing a computer program that, when executed by a processor, implements a method for querying data from a LOB database.

[0087] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. This computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), memory bus direct RAM (DRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0088] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0089] The above-described embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A method for querying data in a LOB database, characterized in that, include: Based on the received database query request, determine the query target and query logic; Based on the query logic, it is determined whether to materialize the intermediate query results generated by the database query request, and a materialization determination result is obtained. If the materialization judgment result is to perform materialization processing, then the LOB locator corresponding to the out-of-row vector data and the in-row business data will be materialized to the preset materialization area; Based on the query target and the query logic, determine whether the out-of-row vector data participates in the corresponding logical operation, and obtain the logical judgment result; If the logical judgment result is that the out-of-row vector data does not participate in the logical operation, then based on the database query request, the corresponding in-row business data is read, and based on the in-row business data, the response result of the database query request is generated; If the logical judgment result indicates that the out-of-row vector data participates in the logical operation, then the LOB locator corresponding to the out-of-row vector data is determined, and the LOB locator is used to indicate the disk storage address of the out-of-row vector data; Query the preset cache area to determine whether there is a memory storage address associated with the LOB locator in the preset cache area. The memory storage address is used to indicate the storage location of the out-of-row vector data in the preset memory area. If the memory storage address exists in the preset cache area, then when participating in the logical operation, the out-of-row vector data in the preset memory area is read based on the memory storage address; If the memory storage address does not exist in the preset cache area, then when participating in the logical operation, the out-of-row vector data is read based on the disk storage address, the out-of-row vector data is stored in the preset memory area, and the memory storage address and the LOB locator are associated and stored in the preset cache area. Based on the read out-of-row vector data, the response result of the database query request is generated.

2. The method according to claim 1, characterized in that, The step of reading the corresponding intra-line business data based on the database query request includes: Based on the database query request, determine whether the in-line business data exists in the page cache area of ​​the LOB database; If it is determined that the in-line business data exists in the page cache area, then the in-line business data is read from the page cache area; If it is determined that the in-line business data does not exist in the page cache area, then the in-line business data is read from the data page corresponding to the LOB database and stored in the page cache area.

3. The method according to claim 1, characterized in that, The step of associating and storing the memory storage address and the LOB locator in the preset cache area includes: The LOB locator is determined as the index key, and the memory storage address is determined as the index value; The memory storage address and the LOB locator are stored in the preset cache area in the form of key-value pairs.

4. The method according to claim 1, characterized in that, Before generating the response result of the database query request based on the read out-of-row vector data, the method further includes: Obtain the maximum cache count corresponding to the out-of-row vector data, and count the current cache count of the out-of-row vector data in the preset cache area; When the current cache count reaches the maximum cache count, clear the memory storage addresses and LOB locators in the preset cache area, clear the corresponding out-of-row vector data in the preset memory area, and reset the current cache count.

5. The method according to claim 4, characterized in that, Before obtaining the maximum cache size corresponding to the out-of-row vector data, the method further includes: Obtain the available memory space of the preset memory region, the data space occupied by the out-of-row vector data, and the available cache space of the preset cache region; Based on the data occupancy space and the available memory space, determine the maximum data storage capacity of the preset memory region; Based on the available cache space and the maximum data storage capacity, determine the maximum cache size corresponding to the out-of-row vector data.

6. A data query device for a LOB database, characterized in that, include: The logic judgment module is used to determine the query target and query logic based on the received database query request; Based on the query logic, it is determined whether to materialize the intermediate query results generated by the database query request, and a materialization determination result is obtained. If the materialization judgment result is to perform materialization processing, then the LOB locator corresponding to the out-of-row vector data and the in-row business data will be materialized to the preset materialization area; Based on the query target and the query logic, determine whether the out-of-row vector data participates in the corresponding logical operation, and obtain the logical judgment result; The first response module is used to read the corresponding in-row business data based on the database query request and generate a response result for the database query request based on the in-row business data if the logical judgment result is that the out-of-row vector data does not participate in the logical operation. The locator determination module is used to determine the LOB locator corresponding to the out-of-row vector data if the logical judgment result is that the out-of-row vector data participates in the logical operation. The LOB locator is used to indicate the disk storage address of the out-of-row vector data. The address query module is used to query a preset cache area to determine whether there is a memory storage address associated with the LOB locator in the preset cache area. The memory storage address is used to indicate the storage location of the out-of-row vector data in the preset memory area. The first reading module is used to read the out-of-row vector data in the preset memory area based on the memory storage address when participating in the logical operation if the memory storage address exists in the preset cache area. The second reading module is used to read the out-of-row vector data based on the disk storage address when participating in the logical operation if the memory storage address does not exist in the preset cache area, store the out-of-row vector data in the preset memory area, and associate the memory storage address and the LOB locator in the preset cache area. The second response module is used to generate a response result for the database query request based on the read out-of-row vector data.

7. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the LOB database data query method as described in any one of claims 1 to 5.

8. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the LOB database data query method as described in any one of claims 1 to 5.

Images