CN114840561A - Implementation method, device, equipment and storage medium for foreign key reference and connection query based on array index - Google Patents
Implementation method, device, equipment and storage medium for foreign key reference and connection query based on array index Download PDFInfo
- Publication number
- CN114840561A CN114840561A CN202210560573.4A CN202210560573A CN114840561A CN 114840561 A CN114840561 A CN 114840561A CN 202210560573 A CN202210560573 A CN 202210560573A CN 114840561 A CN114840561 A CN 114840561A
- Authority
- CN
- China
- Prior art keywords
- referenced
- array
- address
- value
- index
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24553—Query execution of query operations
- G06F16/24558—Binary matching operations
- G06F16/2456—Join operations
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method, a device, equipment and a storage medium for realizing foreign key reference and connection query based on array indexes.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a method, a device, equipment and a storage medium for realizing foreign key reference and connection query based on array index.
Background
In the database, primary keys (primary keys) exist in the referenced tables for being referenced by other tables, wherein the primary keys are unique identifications in the referenced tables; the reference table is a table in which the primary Key value of the referenced table is used as a Foreign Key (Foreign Key), and the Foreign Key and the referenced table can be used for performing correlation query.
That is, the foreign key value of the reference table is the primary key value of the referenced table, and is used to establish association with the referenced table, and at the same time, to maintain data consistency. Conventional databases typically index on the foreign key of the reference table, and are typically implemented using tree data structures such as B-Tree/B + Tree/LSM Tree. For reference relationships, two types of queries that are often executed are: (1) query with equivalent condition filtering based on the foreign key column in the reference table: at this time, the database system can filter through the index on the external key column; (2) an isojoin query operation between the reference and referenced tables based on the reference and referenced columns: and selecting a connection mode from nested circular connection, merging connection or Hash connection according to the difference between the query result list and the record number meeting the equivalent condition to complete the query.
The two types of queries based on the reference relation can achieve better query performance through indexes on the reference columns and the referenced columns. However, when processing data in the reference table, filtering needs to be performed based on the tree structure index (such as B tree/B + tree/LSM tree) on the reference column in the reference table, and multiple comparison operations are required during filtering, which affects further improvement of system performance.
Disclosure of Invention
The technical problem to be solved by the present invention is to provide a method, an apparatus, a device and a storage medium for implementing foreign key reference and connection query based on array index, which can avoid establishing a tree-like index on a reference column and avoid multiple comparison operations on the reference column, thereby providing better two types of typical query performance based on reference relationship.
In order to solve the above technical problem, the present invention provides a method for implementing foreign key reference and join query based on array index, which comprises the following steps:
step 1, automatically recording an increasing sequence number in all values of index records corresponding to main keys or uniqueness constraints by a system, and starting from 0;
step 2, the system appoints to establish an array index structure when creating or modifying the reference table;
step 3, in the running process of the system, a working thread/process in the relational database/KV system data processing system analyzes the received operation request and determines the operation type;
and 4, executing corresponding operation.
Preferably, in step 2, when creating the reference table, automatically creating a reference array index for the reference table by specifying an option, and simultaneously automatically creating a referenced array index for the referenced table; setting external key constraint in the reference table through SQL statements, thereby automatically creating a reference array index and a referenced array index;
the members of the referenced array index are as follows:
[0]|address|latest_ref_fk_id|
[1]|address|latest_ref_fk_id|
......
[N]|address|latest_ref_fk_id|
where the numbers enclosed in parentheses indicate the array subscripts, address indicates the address of the physical record in the reference table in the data file, latest _ ref _ fk _ ID indicates the subscript ID of the last reference array index with the same foreign key value, and-1 indicates that this is the first record with the foreign key value;
the main members of the reference array index structure are as follows:
[0]|address|prev_ref_fk_id|
[1]|address|prev_ref_fk_id|
......
[N]|address|prev_ref_fk_id|
where the bracketed numbers indicate the array indices, address indicates the address of the referenced physical record in the data file, and prev _ ref _ fk _ ID indicates the index ID of the referenced physical record corresponding to the last referenced array index in the referenced array.
Preferably, in step 4, the corresponding operation is specifically executed as follows:
(a) when the operation type is the data inserted into the reference table, recording an increasing serial number in the value of the primary key index/uniqueness index, and after the operation is successful, adding a record to the referenced array, wherein the address of the record is the address of the corresponding physical record in the data file, and the latest _ ref _ fk _ id is-1 and indicates that the record is not referenced;
(b) when the operation type is the data deleted by the reference table, whether the corresponding main key/uniqueness column value is still referred is checked, if the operation type is the data deleted by the reference table, an error is reported, otherwise, the record is deleted, and an address item corresponding to a subscript in the referred array is set to be-1, which indicates that the operation type is deleted;
(c) when the operation type is the data updated by the reference table, if the position of the data storage is not changed, the corresponding array item is not changed, and if the position of the data storage is changed, the Address in the referenced array index item corresponding to the record is updated;
(d) when the operation type is that the reference table inserts data, inserting physical records corresponding to the data, and then finding array units corresponding to subscripts in the referenced array indexes according to the serial numbers recorded in the referenced table;
if the latest _ ref _ fk _ id recorded by the unit is-1, which indicates that the current reference table refers to the value for the first time, an item is added at the last of the reference array, the address value of the item is filled in the address recorded in the data file, the prev _ ref _ fk _ id column of the item is filled in-1, and the array subscript of the item is filled in the latest _ ref _ fk _ id column of the referenced array index;
if the latest _ ref _ fk _ id recorded by the unit is not-1, an entry is appended to the last of the reference array, the address value of the entry is filled in the address recorded in the data file, and the prev _ ref _ fk _ id column of the entry is filled in the latest _ ref _ fk _ id value recorded in the previously referenced array index;
(e) when the operation type is to delete data in the reference table, registering a reference column and address information of the deleted data, then finding a sequence number corresponding to the reference column value in a common index of the referenced table according to the reference column value, then finding a latest _ ref _ fk _ id value in the referenced array index based on a subscript corresponding to the sequence number, finally checking an address value in the reference array index by taking the latest _ ref _ fk _ id value as a subscript, if the address value is matched with the address information of the registered deleted data, modifying the address to be-1, and if the address value is not matched with the address information of the registered deleted data, continuing to find a record of the same previous reference column value in the reference array index according to prev _ ref _ fk _ id until the address information is matched and modified to be-1, namely finishing the deletion operation;
(f) when the operation type is that the data is updated by the reference table, if the position of the data storage is not changed, the corresponding array item is not changed, if the position of the data storage is changed, the Address in the reference array index item corresponding to the record is updated, and if the updated data is a reference column, the operations of deleting and inserting the reference array index and the referenced array index according to the steps (e) and (d) are required to be carried out;
(g) when the operation type is that a reference table record is searched based on a foreign key column, firstly, a referred array subscript ID1 corresponding to a foreign key column value is searched in a main key/unique index of a referred table associated with the foreign key, then, a value ID2 corresponding to latist _ ref _ fk _ ID is obtained from the referred array index, an address value ADDR1 and a PREV _ ref _ fk _ ID value PREV1 are obtained from the ID2 units of a referred array, if the ADDR1 is not-1, the physical record corresponding to the address is a record meeting the condition, then, the record meeting the condition is searched by using the PREV1 as a subscript in the referred array until PREV _ ref _ fk _ ID is-1, all the records are found, and finally, all the physical records meeting the condition are returned to a user based on all the obtained addresses;
(h) when the operation type is an isojoin query that references and is referenced based on the value of the referenced/referenced column, the referenced array index ID1 and the address ADDR1 recorded in the data file are first looked up in the referenced table based on the referenced primary key/unique index, the value ID2 corresponding to the latest _ ref _ fk _ ID is then retrieved from the referenced array index, and obtaining an address value ADDR2 and a PREV _ ref _ fk _ ID value PREV1 in an ID2 unit of the reference array, wherein if ADDR2 is not-1, the physical record corresponding to the address is a qualified record, then continuing to search the qualified record in the reference array by using PREV1 as a subscript until PREV _ ref _ fk _ ID is-1, which indicates that all records have been found, and finally returning all qualified physical records to the user based on all obtained addresses.
Correspondingly, an implementation apparatus for foreign key reference and join query based on array index includes: the system comprises an operation analysis module, an operation execution module, an array index management module and an array index buffer module; the operation analysis module analyzes the received operation request and determines the operation type; the operation execution module completes the operations of inserting, updating and deleting the reference table and the referenced table data based on the external key; the array index management module completes the management of the referenced array index and the referenced array index. When inserting, updating, deleting master and slave table records, the referencing array index and the referenced array index are maintained according to the method mentioned in the first aspect. When connection query is executed based on the reference column value, the reference table and the referred table, query is carried out according to the method provided by the first aspect; array index buffer module: the module is used for caching the data of the reference array index and the referenced array index, and when data modification exists, the modified array data can be written into a disk when the free space of a check point/buffer area is insufficient.
Preferably, the operation execution module completes the operations of inserting, updating and deleting the reference table and the referenced table data based on the foreign key specifically as follows:
(a) when the operation type is the data inserted by the reference table, recording an increasing sequence number in the value of the primary key index/uniqueness index, and finally adding a record to the referenced array after the operation is successful, wherein the address of the record is the address of the corresponding physical record in the data file, and the latest _ ref _ fk _ id is-1 and indicates that the record is not referenced yet;
(b) when the operation type is the data deleted by the reference table, checking whether the corresponding main key/uniqueness column value is still referred, if the data is referred, reporting an error, otherwise, deleting the record, and setting an address item corresponding to a subscript in the referred array as-1 to indicate that the data is deleted;
(c) when the operation type is the data updated by the reference table, no special processing is needed;
(d) when the operation type is that the reference table inserts data, finding an array unit corresponding to a subscript in the referenced array index according to the sequence number recorded in the referenced table;
(e) when the operation type is to delete data in the reference table, registering a reference column and address information of the deleted data, then finding a sequence number corresponding to the reference column value in a common index of the referenced table according to the reference column value, then finding a latest _ ref _ fk _ id value in the referenced array index based on a subscript corresponding to the sequence number, finally checking an address value in the reference array index by taking the latest _ ref _ fk _ id value as a subscript, if the address value is matched with the address information of the registered deleted data, modifying the address to be-1, and if the address value is not matched with the address information of the registered deleted data, continuing to find a record of the same previous reference column value in the reference array index according to prev _ ref _ fk _ id until the address information is matched and modified to be-1, namely finishing the deletion operation;
(f) when the operation type is that the data is updated by the reference table, if the position of the data storage is not changed, the corresponding array item is not changed, if the position of the data storage is changed, the Address in the reference array index item corresponding to the record is updated, and if the updated reference column is a reference column, the operations of deleting and inserting the reference array index and the referenced array index according to the steps (e) and (d) are also required.
(g) When the operation type is to search the reference table record based on the foreign key column, firstly, the referenced array subscript ID1 corresponding to the foreign key column value is found in the primary key/unique index of the referenced table associated with the foreign key, then the value ID2 corresponding to the latist _ ref _ fk _ ID is obtained from the referenced array index, and the address value ADDR1 and the PREV _ ref _ fk _ ID value PREV1 are obtained from the ID2 units of the referenced array, and if the ADDR1 is not-1, the physical record corresponding to the address is a record meeting the condition. Then, the records which meet the conditions are searched in the reference array by taking PREV1 as a subscript until PREV _ ref _ fk _ id is-1, which indicates that all the records are found, and finally, all the physical records which meet the conditions are returned to the user based on all the obtained addresses;
(h) when the operation type is to make an equivalent connection query of a reference table and a referenced table based on the value of a reference/referenced column, firstly, a referenced array index ID1 and an address ADDR1 of the record in the data file are found in the referenced table based on a referenced primary key/unique index, then a value ID2 corresponding to a late _ ref _ fk _ ID is obtained from the referenced array index, an address value ADDR2 and a PREV _ ref _ fk _ ID value PREV1 are obtained from the ID2 units of the referenced array, the ADDR2 is not-1 to indicate that the physical record corresponding to the address is a record with a satisfied condition, and then, the reference array is continued to use PREV1 as a subscript to search for the record with the satisfied condition until PREV _ ref _ fk _ ID is-1 to indicate that all records have been found. And finally, returning all the physical records meeting the conditions to the user based on all the acquired addresses.
Correspondingly, the device for implementing foreign key reference and join query based on array index comprises: one or more processors;
storage means for storing one or more programs, user data;
when the one or more programs are executed by one or more processors, the one or more processors implement the method for implementing array index-based foreign key reference and join query according to any one of the embodiments of the present invention.
Accordingly, a storage medium for implementing array index-based foreign key reference and join query, on which a computer program is stored, is provided, where the computer program implements the method for implementing array index-based foreign key reference and join query according to any one of the embodiments of the present invention when executed by a processor.
The invention has the beneficial effects that: in the invention, the query of the reference table and the referenced table based on the reference column value is directly carried out based on the reference array index and the referenced array index, thereby avoiding a great deal of comparison required by accessing the reference table through a B tree/B + tree/LSM tree and improving the query performance.
Drawings
FIG. 1 is a schematic flow chart of a method of embodiment 1 of the present invention.
Fig. 2 is a schematic flow chart of a method in embodiment 2 of the present invention.
FIG. 3 is a schematic structural diagram of the apparatus of the present invention.
Detailed Description
As shown in fig. 1, a method for implementing foreign key reference and join query based on array index includes the following steps:
s101, automatically recording an increasing sequence number in all values of the index record corresponding to the primary key or the uniqueness constraint by the system, and starting from 0.
S102, the system appoints to establish an array index structure when creating or modifying the reference table: when creating the reference table, automatically creating a reference array index for the reference table by specifying an option (such as create table.. constraint.. for key.. refer places.. with array index, etc.), and automatically creating a referenced array index for the referenced table; the referencing array index and the referenced array index may also be automatically created by setting a foreign key constraint in the reference table through an SQL statement (e.g., an add table.. add constraint.. for the key.. refer entries, etc.).
Assuming that the system has master tables students and slave tables classes inside, the table definitions and initial data are as follows:
Create table students(sno varchar(20),sname varchar(10),age int,primary key(sno))；
insert students values ('S001', 'zhangfei', 15); // assume address 100
Insert students values ('S002', 'li kui', 16); // assume address 200
Create table classes(cno varchar(20),cname varchar(10),stu_no varchar(20),constraint c1 foreign key(stu_no)references students(sno))with array index；
Insert classes values ('C001', 'language', 'S001'); // assume address 300
Insert classes values ('C002', 'math', 'S001'); // assume address 400
Insert into classes values ('C003', 'History', 'S001'); // assume address 500
Insert into classes values ('C004', 'language', 'S002'); // assume address 600
Insert classes values ('C005', 'math', 'S002'); // assume address 700
Insert classes values ('C006', 'physical', 'S002'); // assume address 800
The contents of the referenced array index automatically generated after data insertion are as follows:
array subscript | Address | reference array subscript (late _ ref _ fk _ id) referring to current primary key/unique value
[0]|100|2|
[1]|200|5|
The contents of the reference array index automatically generated after data insertion are as follows:
array subscript | Address | refers to the previous array subscript (prev _ ref _ fk _ id) of the current primary key/unique value
[0]|300|-1|
[1]|400|0|
[2]|500|1|
[3]|600|-1|
[4]|700|3|
[5]|800|4|
S103, the data processing system such as the database/KV receives the processing request insert classes values ('C007', 'English', 'S001'), and determines that the operation type is reference table insertion.
S104, completing the inserting operation in S102 according to the conventional database operation, and at the same time obtaining that the index subscript ID of the primary key corresponding to the newly inserted record in the referenced array index is 0 (corresponding to 'S001'), adding one entry to the newly inserted record in the referenced array index, where the subscript ID is 6, assuming that the address corresponding to the physical record is 900, then completing the following operations by the referenced array index and the referenced array index:
the array index corresponding to the last referencing array index entry latest _ ref _ fk _ id for obtaining the foreign key value from the referenced array index No. 0 cell is 2. Then fill address 900 in the 6 th cell of the reference array index, the previous array index prev _ ref _ fk _ id referencing the current primary key/unique value is 2, then modify the 0 th cell latest _ ref _ fk _ id of the referenced array index to 6, until this completes the modification, and returns the success result to the user.
S105, the working thread/process can write the modification of the external key array index in the memory into the external key array index file when needed, and the persistence is completed.
As shown in fig. 2, a method for implementing foreign key reference and join query based on array index includes the following steps:
s201, the system automatically records an increasing sequence number in all values of the index record corresponding to the primary key or the uniqueness constraint, and starts from 0.
S202, automatically establishing a foreign key array index structure as in S102, and adopting the same initial data as in S102 in the example.
S203, the data processing system such as the database/KV receives a request from students, c.name from students, classes c where s.sno ═ c.stu _ no and s.sno ═ S002', the request is used to find out the name of the student with the school number S002 and the learned course name, where s.sno ═ c.stu _ no indicates that a connection query of the referenced table and the reference table is performed, and it can be determined that the operation type is a query operation based on the reference relationship.
S204, for the query in S203, first, an index entry with a mathematical number S002 is found by using the index of the primary key row sno of the conventional relational database, a leaf node of the index may find that the index subscript ID of the corresponding referred array index is 1, next, the unit No. 1 in the referred array index may find that latest _ ref _ fk _ ID is 5, then, an entry with an ID 5 is found in the referred array index, and prev _ ref _ fk _ ID of the entry may be found to be 4, and an entry with an ID 4 is continuously found in the referred array index, and prev _ ref _ fk _ ID of the entry may be found to be 3, and an entry with an ID 3 is continuously found in the referred array index, and prev _ ref _ fk _ ID of the entry may be found to be-1, which indicates that the search is completed. The ID meeting the conditions in the corresponding referred array index is 1, the ID meeting the conditions in the referred array index is 3, 4 and 5, and the student name and the study course name corresponding to the study number S002 can be found through the address of the corresponding ID items in the referred array index and the referred array index.
And S205, returning the query result meeting the condition to the client.
As shown in fig. 3, an apparatus for implementing foreign key reference and join query based on array index includes: an operation analysis module 301, an operation execution module 302, an array index management module 303, and an array index buffer module 304.
An operation analysis module: the module is responsible for analyzing the received operation request and determining the operation type.
An operation execution module: the module is used for executing the received operation request, and if the operation is related to foreign key reference relationship processing, the module needs to go to a foreign key reference relationship management module to continue execution after the operation is finished.
Array index management module: the module processes related operations of the foreign key adjacency relation, including referenced array index maintenance, connection inquiry based on the reference relation and the like.
Array index buffer module: the module completes the data reading and writing operations of the referenced array index and the referencing array index.
The invention aims to provide an external key reference implementation mode based on array index, which can avoid building tree index on a reference column and avoid multiple comparison operations on the reference column, thereby providing better performance of two types of typical queries based on reference relationship.
Claims (8)
1. A method for realizing foreign key reference and connection query based on array index is characterized by comprising the following steps:
step 1, automatically recording an increasing sequence number in all values of index records corresponding to main keys or uniqueness constraints by a system, and starting from 0;
step 2, the system appoints to establish an array index structure when creating or modifying the reference table;
step 3, in the running process of the system, a working thread/process in the relational database/KV system data processing system analyzes the received operation request and determines the operation type;
and 4, executing corresponding operation.
2. The method for implementing array index-based foreign key reference and join query as claimed in claim 1, wherein in step 2, when creating the reference table, automatically creating a reference array index for the reference table by specifying an option, and simultaneously automatically creating a referenced array index for the referenced table; setting external key constraint in the reference table through SQL statements, thereby automatically creating a reference array index and a referenced array index;
the members of the referenced array index are as follows:
[0]|address|latest_ref_fk_id|
[1]|address|latest_ref_fk_id|
......
[N]|address|latest_ref_fk_id|
where the numbers enclosed in parentheses indicate the array subscripts, address indicates the address of the physical record in the reference table in the data file, latest _ ref _ fk _ ID indicates the subscript ID of the last reference array index with the same foreign key value, and-1 indicates that this is the first record with the foreign key value;
the main members of the reference array index structure are as follows:
[0]|address|prev_ref_fk_id|
[1]|address|prev_ref_fk_id|
......
[N]|address|prev_ref_fk_id|
where the bracketed numbers indicate the array indices, address indicates the address of the referenced physical record in the data file, and prev _ ref _ fk _ ID indicates the index ID of the referenced physical record corresponding to the last referenced array index in the referenced array.
3. The method for implementing foreign key reference and join query based on array index as claimed in claim 1, wherein in step 4, the corresponding operation is executed specifically as:
(a) when the operation type is the data inserted by the reference table, recording an increasing sequence number in the value of the primary key index/uniqueness index, and finally adding a record to the referenced array after the operation is successful, wherein the address of the record is the address of the corresponding physical record in the data file, and the latest _ ref _ fk _ id is-1 and indicates that the record is not referenced yet;
(b) when the operation type is the data deleted by the reference table, whether the corresponding main key/uniqueness column value is still referred is checked, if the operation type is the data deleted by the reference table, an error is reported, otherwise, the record is deleted, and an address item corresponding to a subscript in the referred array is set to be-1, which indicates that the operation type is deleted;
(c) when the operation type is the data updated by the reference table, performing normal operation;
(d) when the operation type is that the data is inserted into the reference table, finding an array unit corresponding to the subscript in the referenced array index according to the sequence number recorded in the referenced table;
(e) when the operation type is deleting data in the reference table, deleting operation is carried out, a reference column and address information of the deleted data are registered, next, a sequence number corresponding to the reference column value is found in a common index of the referenced table according to the reference column value, then, a late _ ref _ fk _ id value is found in the referenced array index based on a subscript corresponding to the sequence number, finally, an address value is checked in the reference array index by taking the late _ ref _ fk _ id value as the subscript, if the address value is matched with the address information of the registered deleted data, the address is modified to be-1, if the address value is not matched, a record of the same previous reference column value in the reference array index is continuously found according to prev _ ref _ fk _ id until the address information is matched and modified to be-1, and then the deleting operation is completed;
(f) when the operation type is that the reference table updates data, if the updated reference column is not the reference column, performing conventional operation, and if the updated reference column is the reference column, performing deletion and insertion operations according to the steps (e) and (d) on the reference array index and the referenced array index;
(g) when the operation type is that a reference table record is searched based on a foreign key column, firstly, a referred array subscript ID1 corresponding to a foreign key column value is searched in a main key/unique index of a referred table associated with the foreign key, then, a value ID2 corresponding to latist _ ref _ fk _ ID is obtained from the referred array index, an address value ADDR1 and a PREV _ ref _ fk _ ID value PREV1 are obtained from the ID2 units of a referred array, if the ADDR1 is not-1, the physical record corresponding to the address is a record meeting the condition, then, the record meeting the condition is searched by using the PREV1 as a subscript in the referred array until PREV _ ref _ fk _ ID is-1, all the records are found, and finally, all the physical records meeting the condition are returned to a user based on all the obtained addresses;
(h) when the operation type is an isojoin query that references and is referenced based on the value of the referenced/referenced column, the referenced array index ID1 and the address ADDR1 recorded in the data file are first looked up in the referenced table based on the referenced primary key/unique index, the value ID2 corresponding to the latest _ ref _ fk _ ID is then retrieved from the referenced array index, and obtaining an address value ADDR2 and a PREV _ ref _ fk _ ID value PREV1 in an ID2 unit of the reference array, wherein if ADDR2 is not-1, the physical record corresponding to the address is a qualified record, then continuing to search the qualified record in the reference array by using PREV1 as a subscript until PREV _ ref _ fk _ ID is-1, which indicates that all records have been found, and finally returning all qualified physical records to the user based on all obtained addresses.
4. The method of claim 3, wherein in step (d), if the latest _ ref _ fk _ id of the cell record is-1, indicating that the current reference table references the value for the first time, an entry is appended to the last of the reference array, the address value of the entry is filled in the address recorded in the data file, the prev _ ref _ fk _ id column of the entry is filled in-1, and the array index of the entry is filled in the latest _ ref _ fk _ id column of the referenced array index;
if the unit records a latest _ ref _ fk _ id that is not-1, then an entry is appended to the last of the reference array, the entry's address value filling in the address referenced in the data file, the entry's prev _ ref _ fk _ id column filling in the latest _ ref _ fk _ id value previously recorded in the referenced array index.
5. An apparatus for implementing foreign key reference and join query based on array index, comprising: the system comprises an operation analysis module, an operation execution module, an array index management module and an array index buffer module; the operation analysis module analyzes the received operation request and determines the operation type; the operation execution module completes the operations of inserting, updating and deleting the reference table and the referenced table data based on the external key; the array index management module completes the management of the referred array index and the referred array index; when the records of the master table and the slave table are inserted, updated and deleted, maintaining a reference array index and a referenced array index according to the method mentioned in the first aspect; when connection query is executed based on the reference column value, the reference table and the referred table, query is carried out according to the method provided by the first aspect; array index buffer module: the module is used for caching the data of the reference array index and the referenced array index, and when data modification exists, the modified array data can be written into a disk when the free space of a check point/buffer area is insufficient.
6. The apparatus for implementing foreign key referencing and join query based on array index as claimed in claim 5, wherein the operation execution module for completing the operations of inserting, updating, and deleting the data of the reference table and the referenced table based on the foreign key is specifically:
(a) when the operation type is the data inserted by the reference table, recording an increasing sequence number in the value of the primary key index/uniqueness index, and finally adding a record to the referenced array after the operation is successful, wherein the address of the record is the address of the corresponding physical record in the data file, and the latest _ ref _ fk _ id is-1 and indicates that the record is not referenced yet;
(b) when the operation type is the data deleted by the reference table, checking whether the corresponding main key/uniqueness column value is still referred, if the data is referred, reporting an error, otherwise, deleting the record, and setting an address item corresponding to a subscript in the referred array as-1 to indicate that the data is deleted;
(c) when the operation type is the data updated by the reference table, no special processing is needed;
(d) when the operation type is that the reference table inserts data, finding an array unit corresponding to a subscript in the referenced array index according to the sequence number recorded in the referenced table;
(e) when the operation type is to delete data in the reference table, registering a reference column and address information of the deleted data, then finding a sequence number corresponding to the reference column value in a common index of the referenced table according to the reference column value, then finding a latest _ ref _ fk _ id value in the referenced array index based on a subscript corresponding to the sequence number, finally checking an address value in the reference array index by taking the latest _ ref _ fk _ id value as a subscript, if the address value is matched with the address information of the registered deleted data, modifying the address to be-1, and if the address value is not matched with the address information of the registered deleted data, continuing to find a record of the same previous reference column value in the reference array index according to prev _ ref _ fk _ id until the address information is matched and modified to be-1, namely finishing the deletion operation;
(f) when the operation type is that the data is updated by the reference table, if the position of the data storage is not changed, the corresponding array item is not changed, if the position of the data storage is changed, the Address in the reference array index item corresponding to the record is updated, and if the updated data is a reference column, the operations of deleting and inserting the reference array index and the referenced array index according to the steps (e) and (d) are required to be carried out;
(g) when the operation type is to search a reference table record based on a foreign key column, firstly finding a referenced array subscript ID1 corresponding to a foreign key column value in a primary key/unique index of a referenced table associated with the foreign key, then acquiring a value ID2 corresponding to latist _ ref _ fk _ ID from the referenced array index, and acquiring an address value ADDR1 and a PREV _ ref _ fk _ ID value PREV1 from ID2 units of a referenced array, wherein if ADDR1 is not-1, the physical record corresponding to the address is a record meeting the condition; then, the records which meet the conditions are searched in the reference array by taking PREV1 as a subscript until PREV _ ref _ fk _ id is-1, which indicates that all the records are found, and finally, all the physical records which meet the conditions are returned to the user based on all the obtained addresses;
(h) when the operation type is an isojoin query based on the values of the reference/referenced columns to make reference and referenced tables, the referenced array index ID1 and the address ADDR1 recorded in the data file are first looked up in the referenced table based on the referenced primary key/unique index, the value ID2 corresponding to the latest _ ref _ fk _ ID is then retrieved from the referenced array index, and obtaining an address value ADDR2 and a PREV _ ref _ fk _ ID value PREV1 in an ID2 unit of the reference array, wherein if ADDR2 is not-1, the physical record corresponding to the address is a qualified record, then continuing to search the qualified record in the reference array by using PREV1 as a subscript until PREV _ ref _ fk _ ID is-1, which indicates that all records have been found, and finally returning all qualified physical records to the user based on all obtained addresses.
7. An apparatus for implementing foreign key reference and join query based on array index, comprising: one or more processors;
a storage device for storing one or more programs, user data;
when the one or more programs are executed by one or more processors, the one or more processors implement the method for implementing array index-based foreign key reference and join query according to any one of the embodiments of the present invention.
8. A storage medium for implementing array index-based foreign key reference and join query, wherein the storage medium stores thereon a computer program, and when the computer program is executed by a processor, the computer program implements the method for implementing array index-based foreign key reference and join query according to any one of the embodiments of the present invention.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210560573.4A CN114840561A (en) | 2022-05-23 | 2022-05-23 | Implementation method, device, equipment and storage medium for foreign key reference and connection query based on array index |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210560573.4A CN114840561A (en) | 2022-05-23 | 2022-05-23 | Implementation method, device, equipment and storage medium for foreign key reference and connection query based on array index |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114840561A true CN114840561A (en) | 2022-08-02 |
Family
ID=82572870
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210560573.4A Pending CN114840561A (en) | 2022-05-23 | 2022-05-23 | Implementation method, device, equipment and storage medium for foreign key reference and connection query based on array index |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114840561A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112835905A (en) * | 2021-02-05 | 2021-05-25 | 上海达梦数据库有限公司 | Indexing method, device, equipment and storage medium for array type column |
-
2022
- 2022-05-23 CN CN202210560573.4A patent/CN114840561A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112835905A (en) * | 2021-02-05 | 2021-05-25 | 上海达梦数据库有限公司 | Indexing method, device, equipment and storage medium for array type column |
| CN112835905B (en) * | 2021-02-05 | 2023-08-01 | 上海达梦数据库有限公司 | Array type column indexing method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US5625815A (en) | Relational database system and method with high data availability during table data restructuring | |
| US8768977B2 (en) | Data management using writeable snapshots in multi-versioned distributed B-trees | |
| AU759360B2 (en) | Database apparatus | |
| US6134543A (en) | Incremental maintenance of materialized views containing one-to-one lossless joins | |
| US5890167A (en) | Pluggable tablespaces for database systems | |
| US7890541B2 (en) | Partition by growth table space | |
| US5873102A (en) | Pluggable tablespaces on a transportable medium | |
| US10754854B2 (en) | Consistent query of local indexes | |
| JP3914662B2 (en) | Database processing method and apparatus, and medium storing the processing program | |
| US9576038B1 (en) | Consistent query of local indexes | |
| US8108431B1 (en) | Two-dimensional data storage system | |
| US20100235344A1 (en) | Mechanism for utilizing partitioning pruning techniques for xml indexes | |
| US7941451B1 (en) | Dynamic preconditioning of a B+ tree | |
| US7363284B1 (en) | System and method for building a balanced B-tree | |
| CN113918663A (en) | Operation method of knowledge graph structure based on naming rule and cache mechanism | |
| CN114840561A (en) | Implementation method, device, equipment and storage medium for foreign key reference and connection query based on array index | |
| US20090132501A1 (en) | Three-dimensional data structure for storing data of multiple domains and the management thereof | |
| CN111460000B (en) | Backtracking data query method and system based on relational database | |
| CN113821508B (en) | Method and system for realizing array index | |
| US6421677B1 (en) | Extension of data definition language (DDL) capabilities for relational databases for applications issuing DDL statements | |
| CA2380348A1 (en) | Method for organizing directories | |
| CN112000666B (en) | Database management system of facing array | |
| CN114218277A (en) | Efficient query method and device for relational database | |
| CN113986591A (en) | Database flashback query method, system, storage medium and equipment based on value log | |
| US10198249B1 (en) | Accessing schema-free databases |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |