An index method, device and medium based on database foreign key constraint checking

Abstract

The application discloses an index method, device and medium based on database foreign key constraint checking, the method comprising: assigning a fixed-capacity foreign key checking buffer for each foreign key constraint when batch data is loaded; extracting foreign key values and row identifiers according to each row foreign key checking request and filling them into the buffer; judging whether the buffer meets the forced refresh trigger condition; when the condition is met, aggregating the foreign key values to be checked in the buffer to construct a batch search key set, calling a batch index detection interface to perform single index tree traversal on the parent table index; mapping the detection result back to each checking unit to determine the constraint verification state of each row of data; when there is a verification failure, the operation is aborted and rolled back, and when all pass, the buffer is reset for reuse. The application ensures controllable memory through the fixed-capacity buffer and instant batch detection, avoids the random I / O overhead of row-by-row checking and the memory explosion problem of the existing batch scheme, and significantly improves the foreign key checking performance in the batch data import scene.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to an indexing method, device and medium based on database foreign key constraint checking. Background Technology

[0002] Relational databases maintain referential integrity between tables through foreign key constraints. In scenarios such as ETL data loading and historical data migration, batch data import has become one of the core operations of the database, and foreign key constraint checking is the main performance bottleneck in such scenarios.

[0003] Existing foreign key constraint checking techniques mainly employ a row-by-row foreign key checking scheme used by mainstream databases such as PostgreSQL, triggering a parent table primary key probe separately when each row of data is inserted. Because foreign key values ​​in batch-imported data generally exhibit high locality, row-by-row independent probes cannot aggregate access requests to the same index page, leading to frequent random I / O to index pages, a sharp drop in database buffer hit rate, and overall performance degrading linearly with the amount of data.

[0004] Another method for checking foreign key constraints is the sort-and-merge optimization technique, which eliminates random I / O by sorting all foreign key values ​​and then merging them with the parent table index. However, this approach requires collecting and sorting all foreign key values. When the data volume reaches tens of millions or the foreign keys are of variable length, it can easily trigger disk sorting, leading to I / O storms and exhaustion of temporary tablespaces, severely impacting database stability. Summary of the Invention

[0005] To address the technical problems existing in the background art, embodiments of this application provide an indexing method, device, and medium based on database foreign key constraint checking. The method includes: when the database is in a batch data loading scenario, allocating a foreign key check buffer of preset capacity for each foreign key constraint in the current session; the foreign key check buffer includes a row counter and a row data slot array; the foreign key check buffer has a preset capacity limit; according to each row foreign key check request of the sub-table data loading operation, extracting the foreign key value and row identifier of each row of sub-table data to form a check unit, and filling the check unit into the foreign key check buffer of the corresponding foreign key constraint; and real-time determining whether the state of the foreign key check buffer meets a preset forced refresh trigger condition; if not, continuing to check the foreign key check buffer. Fill; when satisfied, extract all foreign key values ​​to be checked from the foreign key check buffer, construct a batch search key set, and call the batch index probe interface to perform batch index probe on the parent table index to complete the existence verification of each search key in the batch search key set and generate batch probe results; the search key is bound one-to-one with the foreign key value to be checked; the batch probe results are mapped back to all check units in the foreign key check buffer to determine the foreign key constraint verification status of each row of sub-table data; when the foreign key constraint verification of sub-table data fails, the current batch data loading operation is stopped, transaction rollback is performed and the foreign key check buffer is cleared; when the foreign key constraint verification of all sub-table data passes, the row count counter of the foreign key check buffer is reset for subsequent reuse.

[0006] In one example, before filling the foreign key check buffer corresponding to the foreign key constraint with the check unit, the method further includes: using a hash table to detect whether the same foreign key value already exists in the foreign key check buffer; if the same foreign key value exists, recording the mapping relationship between the row identifier of the check unit and the existing check item, without incrementing the row count counter; if the same foreign key value does not exist, storing the check unit in the row data slot array of the foreign key check buffer, and incrementing the row count counter.

[0007] In one example, the status of the foreign key check buffer is determined in real time to see if it meets the preset forced refresh trigger condition. If not, the foreign key check buffer continues to be filled. Specifically, this includes: real-time detection of the row count counter value, data retention time, and current transaction execution status of the foreign key check buffer; when the row count counter has not reached the preset capacity limit of the foreign key check buffer, and the difference between the current time and the last refresh timestamp of the foreign key check buffer has not exceeded the preset timeout threshold, and the current transaction has not been completed, it is determined that the preset forced refresh trigger condition is not met, and the foreign key check buffer continues to be filled.

[0008] In one example, when the conditions are met, all foreign key values ​​to be checked in the foreign key check buffer are extracted, a batch search key set is constructed, and the batch index probe interface is called to perform batch index probe on the parent table index to complete the existence verification of each search key in the batch search key set and generate batch probe results. Specifically, this includes: when the preset forced refresh trigger condition is met, extracting all foreign key values ​​to be checked in the foreign key check buffer and their corresponding row identifier mapping relationships; performing deduplication on the foreign key values ​​to be checked to obtain a foreign key value set, while retaining the row identifier associated with each foreign key value; if the parent table index is a B-Tree index, sorting the foreign key value set in ascending order of key values ​​to generate a batch search key set; binding the search key one-to-one with the foreign key value to be checked; if the parent table index is not a B-Tree index, directly using the foreign key value set as the batch search key set. The search key is bound one-to-one with the foreign key value to be checked; the batch index probing interface is called, and a single index tree traversal from the root node to the leaf node is performed on the parent table index through the batch search key set, while maintaining the cursor of the search key to be verified during the traversal; when any leaf node is accessed, a batch binary search is performed on all index tuples stored in the leaf node to verify the existence status of all search keys within the key value coverage range of the leaf node, and the existence status is marked; after the traversal is completed, the batch probing results are generated according to the existence status marks of all search keys and the row identifier mapping relationship.

[0009] In one example, the method further includes: during the execution of batch index probing, detecting whether a preset exception occurs; when any preset exception is detected, reverting to row-by-row checking mode; the preset exceptions include index probing returning an error, foreign key check buffer memory allocation failure, foreign key value data type or length exceeding the range supported by batch processing, concurrent data definition language operations modifying the parent table index structure, and the parent table index being deleted or rebuilt during batch probing; when reverting to row-by-row checking mode, clearing unverified search keys in the batch search key set in the current foreign key check buffer; maintaining the verification results for search keys that have passed existence verification, and performing foreign key constraint checks on subsequent new search keys using row-by-row index probing logic until the current batch data loading operation ends.

[0010] In one example, mapping the batch probe results back to all check units in the foreign key check buffer to determine the foreign key constraint verification status of each row of sub-table data specifically includes: parsing the batch probe results and constructing a check result mapping table; the check result mapping table uses foreign key values ​​as keys and corresponding existence status markers as values; iterating through each check unit in the foreign key check buffer and querying the check result mapping table based on the foreign key value in the check unit to determine the foreign key constraint verification status of each row of sub-table data; when the foreign key value exists in the parent table index, marking the foreign key constraint of the sub-table data corresponding to the foreign key value as verified successfully; when the foreign key value does not exist in the parent table index, marking the foreign key constraint of the sub-table data corresponding to the foreign key value as verified unsuccessfully.

[0011] In one example, when a foreign key constraint validation fails in a sub-table, the current batch data loading operation is aborted, a transaction rollback is executed, and the foreign key check buffer is cleared. Specifically, this includes: when a foreign key constraint validation fails in a sub-table, locating the sub-table data row information that fails the foreign key constraint based on the failed foreign key value and row identifier; aborting the batch data loading operation of the sub-table data row information, executing a transaction rollback, clearing all data to be checked in the foreign key check buffer, and resetting the row counter of the foreign key check buffer to zero.

[0012] In one example, the method also includes: when there are multiple foreign key constraints, using multiple parallel worker threads to simultaneously perform batch index probes on the foreign key check buffers corresponding to different foreign key constraints; summarizing the batch probe results of all foreign key constraints, and triggering an error report if any foreign key constraint check fails.

[0013] On the other hand, embodiments of this application provide an indexing device based on database foreign key constraint checking, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform any of the above-mentioned indexing methods based on database foreign key constraint checking.

[0014] On the other hand, embodiments of this application provide a non-volatile computer storage medium for indexing based on database foreign key constraint checking, which stores computer-executable instructions capable of executing any of the above-mentioned indexing methods based on database foreign key constraint checking.

[0015] The above-described technical solutions adopted in the embodiments of this application can achieve the following beneficial effects: By introducing a fixed-capacity foreign key check buffer, memory usage becomes deterministic and controllable. Regardless of the size of the batch imported data, the memory usage of each foreign key constraint remains constant, avoiding the risk of existing sorting and merging schemes triggering external disk sorting and consuming large amounts of temporary table space under large data volumes. In terms of performance, traditional row-by-row index probing is transformed into batch probing. Through a deduplication mechanism within the buffer, multiple checks of the same foreign key value are aggregated into a single index access. For B-Tree indexes, sorting optimization and batch binary search in a single tree traversal are employed, significantly reducing the number of random I / O operations on index pages and the number of B-Tree traversals, improving CPU cache hit rate. Furthermore, this invention does not maintain a hash state table or Bloom filter across transactions; each batch probe directly accesses the latest state of the parent table index, avoiding the high overhead and consistency problems caused by state synchronization. In addition, the entire solution is implemented only at the foreign key constraint check layer, requiring no modification to the query compiler or executor, making it highly compatible with existing database architectures, low intrusive, and easy to integrate and maintain. Attached Figure Description

[0016] To more clearly illustrate the technical solution of this application, some embodiments of this application will be described in detail below with reference to the accompanying drawings, in which: Figure 1 A flowchart illustrating an indexing method based on database foreign key constraint checking, provided for an embodiment of this application; Figure 2 This is a schematic diagram of the structure of an indexing device based on database foreign key constraint checking, provided as an embodiment of this application. Detailed Implementation

[0017] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0018] Some embodiments of this application will now be described in detail with reference to the accompanying drawings.

[0019] Figure 1 This is a flowchart illustrating an indexing method based on database foreign key constraint checking, provided as an embodiment of this application. This method can be applied to different business domains. Certain input parameters or intermediate results in this process can be manually adjusted to help improve accuracy.

[0020] The analysis method involved in the embodiments of this application can be implemented by a terminal device or a server, and this application does not impose any special limitations on it. For ease of understanding and description, the following embodiments are all described in detail using a server as an example.

[0021] Based on this Figure 1 The process may include the following steps: S101: When the database is in a batch data loading scenario, allocate a foreign key check buffer of preset capacity for each foreign key constraint in the current session; the foreign key check buffer includes a row count counter and a row data slot array; the foreign key check buffer has a preset capacity limit.

[0022] In some embodiments of this application, the system first identifies whether the database is in a batch data loading scenario. Specifically, the current scenario is identified as a batch data loading scenario based on any of the following conditions: the currently executed command is a COPY FROM batch import command; or the currently executed command is an INSERT...SELECT statement and the estimated number of source data rows exceeds a preset threshold; or the number of rows inserted or updated in the current transaction accumulates to exceed a buffer trigger threshold under a single foreign key constraint. When any of the above conditions are met, the system determines that it has entered batch data loading mode.

[0023] Subsequently, a separate foreign key check buffer is allocated for each active foreign key constraint in the current session. The foreign key check buffer has a strict fixed capacity limit, with a default value of 64 rows and a maximum of 128 rows. Specifically, the foreign key check buffer includes an array of row data slots to store the foreign key value and row identifier for each row; a row counter to record the number of rows to be checked in the current buffer; a parent table object identifier; an aggregated array of foreign key values ​​for batch index probing; and a timestamp from the last buffer refresh for timeout triggering.

[0024] Furthermore, the buffer size can be dynamically adjusted within a fixed upper limit based on the foreign key's data type and system memory pressure: for single-column integer foreign keys, a fixed 64 rows are used; for multi-column composite foreign keys or variable-length foreign keys, the threshold is dynamically calculated based on the total width of the foreign keys, using the following formula:

[0025] Here, key_width is the average width of the foreign key column, ensuring that the memory usage of a single buffer does not exceed 2KB.

[0026] S102: Based on the foreign key check request for each row of the sub-table data loading operation, extract the foreign key value and row identifier of each row of sub-table data to form a check unit, and fill the check unit into the foreign key check buffer corresponding to the foreign key constraint.

[0027] In some embodiments of this application, when a foreign key constraint is encountered during data loading operations such as INSERT or COPY, index probing is not triggered immediately. Instead, the foreign key check request is routed to the foreign key check buffer manager.

[0028] Specifically, for each row of sub-table data to be inserted, the foreign key column value is extracted according to the foreign key definition. That is, for a single-column foreign key, the Datum value of that column is directly extracted; for a composite foreign key, the values ​​of multiple columns are extracted and a composite key structure is constructed. At the same time, the row identifier of the data (such as TID, row number, or row number in the COPY command) is obtained, and the extracted foreign key value and the row identifier are combined into a check unit.

[0029] Before filling the foreign key check buffer corresponding to the foreign key constraint with the check unit, a fast deduplication check is performed, using a hash table to check if the same foreign key value already exists in the current buffer. If the same key value is detected, only the mapping relationship between the current row identifier and the existing check item is recorded, without incrementing the row count counter, thereby further reducing the number of subsequent index probes; if the same foreign key value does not exist, the check unit is stored in the row data slot array of the foreign key check buffer, and the row count counter is incremented.

[0030] S103: In real time, determine whether the state of the foreign key check buffer meets the preset forced refresh trigger condition. If not, continue to fill the foreign key check buffer.

[0031] In some embodiments of this application, the row count counter value, data retention time, and current transaction execution status of the foreign key check buffer are monitored in real time. Specifically, it is checked whether the row count counter has reached the preset capacity limit of the foreign key check buffer; whether the difference between the current time and the last refresh timestamp of the foreign key check buffer exceeds a preset timeout threshold; and whether the current transaction has been committed, rolled back, or the batch data loading operation has been completed.

[0032] If none of the above three conditions are met—that is, the row count counter has not reached the preset capacity limit of the foreign key check buffer, the difference between the current time and the last refresh timestamp of the foreign key check buffer has not exceeded the preset timeout threshold, and the current transaction has not been completed—then the preset forced refresh triggering conditions are determined not to be met, and the foreign key check buffer continues to be filled to process the next row of data. Conversely, if any of the triggering conditions are met, the filling process is immediately exited and a batch index probe is triggered.

[0033] S104: When the condition is met, extract all foreign key values ​​to be checked from the foreign key check buffer, construct a batch search key set, and call the batch index probe interface to perform batch index probe on the parent table index to complete the existence verification of each search key in the batch search key set and generate batch probe results; the search key is bound one-to-one with the foreign key value to be checked.

[0034] In some embodiments of this application, when a preset forced refresh trigger condition is met, all foreign key values ​​to be checked and their corresponding row identifier mapping relationships are first extracted from the foreign key check buffer. Then, deduplication processing is performed on the foreign key values ​​to be checked to obtain a deduplicated set of foreign key values, while retaining all row identifiers associated with each foreign key value.

[0035] Furthermore, differentiated processing is performed based on the type of the parent table index. If the parent table index is a B-Tree index, the foreign key value set is sorted in ascending order of the key values ​​to generate a sorted batch search key set, with each search key maintaining a one-to-one binding relationship with the foreign key value to be checked. If the parent table index is not a B-Tree index, the foreign key value set is directly used as the batch search key set.

[0036] Subsequently, the batch index probing interface `index_batch_probe(Relation indexRel, BatchKeys keys, int nkeys)` is called. This performs a single index tree traversal from the root node to the leaf node on the parent table index using a batch set of search keys. During the traversal, a cursor is maintained for each search key to be verified, indicating the range of keys currently being processed. When any leaf node is accessed, a batch binary search is performed on all index tuples stored within that leaf node to verify the existence of all search keys within the key value range covered by that leaf node, and the existence status of each search key is marked.

[0037] This method, combining a single tree traversal with batch binary search of leaf nodes, fully utilizes the physical order of the index pages, significantly reducing the number of page accesses. After the entire index tree traversal is complete, a batch probe result is generated based on the existence status markers of all search keys and the preserved row identifier mapping relationship. This result records whether each foreign key value exists in the parent table index.

[0038] S105: Map the batch probe results back to all the check units in the foreign key check buffer to determine the foreign key constraint verification status of each row of sub-table data.

[0039] In some embodiments of this application, the batch probe results are first parsed to construct an inspection result mapping table. This inspection result mapping table uses foreign key values ​​as keys and corresponding existence status flags as values. Then, each inspection unit in the foreign key inspection buffer is traversed. For each inspection unit, the inspection result mapping table is queried based on its foreign key value to determine the foreign key constraint verification status of that row of sub-table data.

[0040] When a foreign key value is found to exist in the parent table index, the foreign key constraint of the corresponding child table data is marked as valid; when a foreign key value is found not to exist in the parent table index, the foreign key constraint of the corresponding child table data is marked as invalid.

[0041] When multiple row identifiers are mapped to the same foreign key value due to deduplication, the existence status of that foreign key value will be applied to all associated row identifiers simultaneously. In this way, foreign key constraint verification of all rows in the buffer can be efficiently completed with the result of a single batch probe, avoiding the overhead of probing each row independently.

[0042] S106: When the foreign key constraint validation of sub-table data fails, the current batch data loading operation is aborted, the transaction is rolled back, and the foreign key check buffer is cleared.

[0043] In some embodiments of this application, when any foreign key constraint in a sub-table is marked as failing validation, the error handling process is immediately initiated. First, based on the failing foreign key value and row identifier, the row information of the sub-table data that fails the foreign key constraint is precisely located. Specifically, for COPY import scenarios, the row number and filename are used for location; for INSERT...SELECT statements, the row identifier or row number of the source data row is used for location; and for regular INSERT statements, the violated foreign key column value is directly reported.

[0044] The system outputs error messages in a format consistent with the database's native row-by-row check mode. Upon outputting the error message, it immediately aborts the current batch data loading operation, performs a transaction rollback, and ensures data consistency. Simultaneously, it clears all data to be checked in the foreign key check buffer, resets the row counter of the foreign key check buffer to zero, and releases relevant memory resources to prevent dirty data from affecting subsequent operations.

[0045] S107: When all foreign key constraint validations of the sub-table data pass, reset the row count counter of the foreign key check buffer for subsequent reuse.

[0046] In some embodiments of this application, when the verification result shows that all rows in the buffer have passed the foreign key constraint check, a cleanup and reuse operation is performed. Specifically, the row count counter of the foreign key check buffer is cleared and reset to 0, while the allocated buffer memory is reserved for subsequent reuse to avoid the overhead of frequent memory allocation and release.

[0047] Secondly, the last refresh timestamp of the foreign key check buffer is updated to the current time for subsequent timeout triggering. Afterward, processing of subsequent batch data rows continues until the entire batch data loading operation is complete. When the transaction is finally committed, the system checks the buffer again to ensure it is empty, guaranteeing that all inserted data has passed foreign key constraint validation before allowing the transaction to commit.

[0048] In this way, the fixed-capacity foreign key check buffer works efficiently throughout the entire batch data loading session, maintaining deterministic memory usage and optimizing foreign key check performance for large-scale data import scenarios.

[0049] It should be noted that, although the embodiments in this application are based on... Figure 1 Steps S101 to S107 will be described sequentially, but this does not mean that steps S101 and S107 must be performed in a strict order. The reason this embodiment follows this order is... Figure 1 The order in which steps S101 to S107 are described is provided to facilitate understanding of the technical solutions of the embodiments of this application by those skilled in the art. In other words, in the embodiments of this application, the order of steps S101 to S107 can be appropriately adjusted according to actual needs.

[0050] pass Figure 1This method introduces a fixed-capacity foreign key check buffer, making memory usage deterministic and controllable. Regardless of the size of the batch imported data, the memory usage of each foreign key constraint remains constant, avoiding the risk of existing sorting and merging schemes triggering external disk sorting and consuming large amounts of temporary table space under large data volumes. In terms of performance, it transforms traditional row-by-row index probing into batch probing. Through a deduplication mechanism within the buffer, multiple checks of the same foreign key value are aggregated into a single index access. For B-Tree indexes, sorting optimization and batch binary search in a single tree traversal are employed, significantly reducing the number of random I / O operations on index pages and the number of B-Tree traversals, improving CPU cache hit rate. Furthermore, this invention does not maintain a hash state table or Bloom filter across transactions; each batch probe directly accesses the latest state of the parent table index, avoiding the high overhead and consistency problems caused by state synchronization. In addition, the entire solution is implemented only at the foreign key constraint check layer, requiring no modification to the query compiler or executor, making it highly compatible with existing database architectures, low intrusive, and easy to integrate and maintain.

[0051] Figure 2 A schematic diagram of an indexing device based on database foreign key constraint checking provided in this application embodiment includes: At least one processor; and, A memory that is communicatively connected to at least one processor; wherein, The memory stores instructions that can be executed by at least one processor, which enable at least one processor to perform any of the above-mentioned indexing methods based on database foreign key constraint checks.

[0052] Some embodiments of this application provide an indexing non-volatile computer storage medium based on database foreign key constraint checking, which stores computer-executable instructions capable of executing any of the above-mentioned indexing methods based on database foreign key constraint checking.

[0053] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0054] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0055] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0056] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0057] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0058] These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable apparatus for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0059] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0060] Memory may include non-persistent storage in computer-readable media, random access memory (RAM), and non-volatile memory such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0061] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0062] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0063] The above are merely embodiments of this application and are not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, or improvements made within the technical principles of this application should fall within the protection scope of this application.

Claims

1. An indexing method based on database foreign key constraint checking, characterized in that, The method includes: When the database is in a batch data loading scenario, a foreign key check buffer of preset size is allocated for each foreign key constraint in the current session; the foreign key check buffer includes a row count counter and a row data slot array; the foreign key check buffer has a preset capacity limit. Based on the foreign key check request for each row of the sub-table data loading operation, extract the foreign key value and row identifier of each row of sub-table data to form a check unit, and fill the check unit into the foreign key check buffer of the corresponding foreign key constraint. The system continuously determines whether the state of the foreign key check buffer meets the preset forced refresh trigger condition. If the condition is not met, the system continues to fill the foreign key check buffer. When the conditions are met, all foreign key values ​​to be checked are extracted from the foreign key check buffer, a batch search key set is constructed, and the batch index probe interface is called to perform batch index probe on the parent table index to complete the existence verification of each search key in the batch search key set and generate batch probe results; the search key is bound one-to-one with the foreign key value to be checked. The batch probe results are mapped back to all the check units in the foreign key check buffer to determine the foreign key constraint verification status of each row of sub-table data. When the foreign key constraint validation fails for data in a sub-table, the current batch data loading operation is aborted, the transaction is rolled back, and the foreign key check buffer is cleared. When all foreign key constraint validations for the data in the child tables pass, the row count counter in the foreign key check buffer is reset for later reuse.

2. The method according to claim 1, characterized in that, Before filling the foreign key check buffer corresponding to the foreign key constraint with the check unit, the method further includes: Use a hash table to check if the same foreign key value already exists in the foreign key check buffer; If the same foreign key value exists, record the mapping relationship between the row identifier of the inspection unit and the existing inspection item, and do not increment the row counter; If no identical foreign key value exists, the checking unit is stored in the row data slot array of the foreign key checking buffer, and the row count counter is incremented.

3. The method according to claim 1, characterized in that, The real-time determination of whether the state of the foreign key check buffer meets the preset forced refresh trigger condition, and if not, continuing to fill the foreign key check buffer, specifically includes: Real-time monitoring of the row count counter value, data retention time, and current transaction execution status of the foreign key check buffer; When the row count counter has not reached the preset capacity limit of the foreign key check buffer, and The difference between the current time and the last refresh timestamp of the foreign key check buffer does not exceed the preset timeout threshold, and If the current transaction has not been completed, it is determined that the preset forced refresh trigger condition is not met, and the foreign key check buffer continues to be filled.

4. The method according to claim 1, characterized in that, When the condition is met, all foreign key values ​​to be checked are extracted from the foreign key check buffer, a batch search key set is constructed, and the batch index probe interface is called to perform batch index probe on the parent table index to complete the existence verification of each search key in the batch search key set and generate batch probe results, specifically including: When the preset forced refresh trigger condition is met, extract all foreign key values ​​to be checked and their corresponding row identifier mapping relationships from the foreign key check buffer; The foreign key values ​​to be checked are deduplicated to obtain a set of foreign key values, while retaining the row identifier associated with each foreign key value; If the parent table index is a B-Tree index, the set of foreign key values ​​is sorted in ascending order of key values ​​to generate a batch search key set; the search key is bound one-to-one with the foreign key value to be checked. If the parent table index is not a B-Tree index, the set of foreign key values ​​is directly used as the set of batch search keys; the search keys are bound one-to-one with the foreign key values ​​to be checked. The batch index probing interface is called, and a single index tree traversal from the root node to the leaf node is performed on the parent table index through the batch search key set, while maintaining the cursor of the search key to be verified during the traversal. When any leaf node is accessed, a batch binary search is performed on all index tuples stored in the leaf node to verify the existence of all search keys within the key value coverage of the leaf node, and the existence status is marked. Once the traversal is complete, a batch detection result is generated based on the existence status flags of all search keys and the row identifier mapping relationship.

5. The method according to claim 1, characterized in that, The method further includes: During the batch index probing process, it checks for any preset abnormal conditions. If any preset abnormal condition is detected, it reverts to the row-by-row inspection mode. The preset abnormal conditions include: index probe returns an error, foreign key check buffer memory allocation fails, the data type or length of the foreign key value exceeds the range supported by batch processing, concurrent data definition language operations modify the parent table index structure, and the parent table index is deleted or rebuilt during batch probe. When reverting to line-by-line inspection mode, clear any unverified search keys in the batch search key set in the current foreign key inspection buffer; For search keys that have passed existence verification, retain the verification results, and for subsequent new search keys, use row-by-row index probing logic to check foreign key constraints until the current batch data loading operation is completed.

6. The method according to claim 1, characterized in that, The step of mapping the batch probe results back to all the check units in the foreign key check buffer to determine the foreign key constraint verification status of each row of sub-table data specifically includes: Parse the batch detection results and construct an inspection result mapping table; the inspection result mapping table uses keys as keys and corresponding existence status markers as values. Iterate through each check unit in the foreign key check buffer, and query the check result mapping table according to the foreign key value in the check unit to determine the foreign key constraint verification status of each row of sub-table data; When a foreign key value exists in the parent table index, the foreign key constraint of the child table data corresponding to the foreign key value is marked as valid. When a foreign key value does not exist in the parent table index, the foreign key constraint of the child table data corresponding to the foreign key value is marked as failing the validation.

7. The method according to claim 1, characterized in that, When the foreign key constraint validation fails for data in a sub-table, the current batch data loading operation is aborted, a transaction rollback is performed, and the foreign key check buffer is cleared. Specifically, this includes: When a foreign key constraint validation fails in a sub-table, locate the sub-table data row information that fails the foreign key constraint validation based on the foreign key value that failed the validation and the row identifier. The batch data loading operation of the sub-table data row information is aborted, the transaction is rolled back, all data to be checked in the foreign key check buffer is cleared, and the row count counter of the foreign key check buffer is reset to zero.

8. The method according to claim 1, characterized in that, The method further includes: When multiple foreign key constraints exist, multiple parallel worker threads are used to simultaneously perform batch index probing of the foreign key check buffers corresponding to different foreign key constraints; Summarize the batch detection results of all foreign key constraints. If any foreign key constraint fails to be checked, trigger an error report.

9. An indexing device based on database foreign key constraint checking, characterized in that, include: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform an indexing method based on database foreign key constraint checking as described in any one of claims 1-8.

10. An index storage medium based on database foreign key constraint checking, storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of executing an indexing method based on database foreign key constraint checking as described in any one of claims 1-8.

Images