Realization method and query method for materialized adjacency index of relational database

Abstract

The invention discloses a realization method and a query method for a materialized adjacency index of a relational database, and the method comprises the steps: introducing the materialized adjacency index in the relational database to identify the relation between different records in two tables, storing the connection relation between the two tables in the materialized adjacency index, and identifying a specific relation through a RowID partner pair; the materialized adjacency index is created according to the following steps: creating an adjacency index, and performing structured organization on data through the adjacency index; performing adjacency relation query by utilizing the adjacency index to obtain an adjacency data set; materializing the adjacent data set into an adjacent index to realize that the record in the materialized adjacent index is an edge in the graph model, and each edge passes through RowID-gt; and performing RowID marking.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of relational databases, and particularly relates to a materialized adjacency index implementation method of a relational database and a query method thereof. BACKGROUND

[0002] ER model (Entity-Relationship Model) is an abbreviation of Entity-relationship model, which can help people describe the connection between things in the real world and guide database design.

[0003] An entity is an object in the real world, which can be specific to people, things, and objects. For example: a teacher. An attribute refers to a certain characteristic possessed by an entity, which is called an attribute. For example, "number", "name", and "major" are attribute descriptions. A key refers to an attribute that can uniquely identify each entity among all attributes describing an entity set, which is called a key. Connection: Nothing in the world exists in isolation, and there are connections within and between things, such as teaching. A simple model, teacher-teach-student. This forms a simple model, where the teacher and student are entities, and teach is the connection between entities.

[0004] A typical student taught by a teacher belongs to which department? A multi-table join query needs to be performed with the help of a relationship table. By connecting the teacher to the relationship table "teach", and then connecting to the student table with the help of the relationship table, the department to which the student belongs is confirmed. A typical two-connection operation is performed, and the query efficiency is not high. SUMMARY

[0005] The application aims to solve the problem of low query efficiency in existing multi-table join queries. The application proposes a materialized adjacency index implementation method of a relational database and a query method thereof. The materialized technology is combined with adjacency index, and the materialized adjacency index enables the relational database to use graph technology to improve the speed of multi-table join queries and gives the relational database graph index capability, thereby significantly improving the efficiency of multi-table join queries.

[0006] Technical solution: A materialized adjacency index implementation method of a relational database, comprising: introducing a materialized adjacency index in a relational database to identify the connection between different records in two tables. The materialized adjacency index stores the connection relationship between the two tables, and a RowID partner pair is used to identify a specific relationship.

[0007] The materialized adjacency index is created according to the following steps:

[0008] An adjacency index is created to structurally organize data through the adjacency index.

[0009] An adjacency relationship query is performed using the adjacency index to obtain an adjacency data set.

[0010] Materialize the adjacency dataset into the adjacency index, realize the record in the materialized adjacency index as the edge in the graph model, and each edge is marked by RowID->RowID.

[0011] Further, the adjacency data is updated by periodically rebuilding the materialized adjacency index.

[0012] Further, the adjacency index is used to structurally organize the data, specifically including that the adjacency index uses a B-tree, a B+ tree or an LSM tree to structurally organize the data.

[0013] Further, the adjacency index is used to perform adjacency relationship query to obtain the adjacency dataset, specifically including that the adjacency relationship query is performed by combining the index [RowID, RowID] to obtain the adjacency dataset.

[0014] Further, when the relationship data of the external table is queried in the adjacency relationship, the relationship data of the external table is uniformly mapped to the relationship table in the relational database, and then the adjacency relationship query is performed.

[0015] The application discloses a query method of a materialized adjacency index based on a relational database, and the method comprises the following steps:

[0016] According to the above-mentioned materialized adjacency index implementation method of the relational database, the materialized adjacency index is introduced into the relational database.

[0017] By locating the RowID of the known table, scanning is directly performed on the materialized adjacency index to obtain the query result.

[0018] Further, for the newly added adjacency relationship, the following strategy is adopted:

[0019] For transactional applications, when the newly added adjacency relationship does not exceed the set value, the newly added adjacency relationship is first loaded into the memory, and then the newly added adjacency relationship is processed; when the newly added adjacency relationship exceeds the set value, the materialized adjacency index is rebuilt to update the data of the materialized adjacency index.

[0020] For analytical applications, the newly added adjacency relationship is ignored, and the user is supported to specify the correlation query of the newly added adjacency relationship.

[0021] Advantages: compared with the prior art, the application has the following advantages:

[0022] (1) The application provides the ability to support adjacency query in the relational database, thereby expanding the graph query function of the relational database.

[0023] (2) The method can significantly improve the performance of multi-table query and realize fast retrieval.

[0024] (3) The materialization index technology introduced in the present application effectively reduces the computing resources required for multi-table join queries, further enhancing query speed;

[0025] (4) The materialization index technology introduced in the present application in the relational database enables the relational database to have persistent storage capability of relations (edges), and the data source can be an internal relation table or an external table mapped from external relation data;

[0026] (5) The materialization index technology introduced in the present application in the relational database optimizes the multi-table join query capability of traditional relational data, and optimizes seven-table join query into three-materialization-adjacent-index adjacent query, greatly improving the retrieval capability;

[0027] (6) The materialization index technology introduced in the present application in the relational database breaks through the graph index capability of the relational database and has native multi-hop adjacent query capability, which can extend the support of the graph mode. BRIEF DESCRIPTION OF DRAWINGS

[0028] Figure 1 is a 3-hop adjacent query schematic diagram;

[0029] Figure 2 is an ER model schematic diagram. DETAILED DESCRIPTION

[0030] Materialized view is a special relational database object that can store query results and allow users to access these results like ordinary tables. The advantage of materialized view is that it can significantly improve query performance, especially when dealing with complex queries and large amounts of data. Materialized view is a storage of query results, which contains a physical copy of query data. This means that materialized view not only improves query performance, but also reduces the pressure on the original table.

[0031] In order to speed up the query, the present embodiment proposes a method based on materialization adjacent index technology to improve the multi-table join query capability and graph retrieval capability of the database. Before introducing the materialization adjacent index, the background knowledge relied on in the system needs to be introduced first.

[0032] Each row of data in a table has a key, and the unique identification of a row is recorded as RowID, and the primary key and RowID are in one-to-one mapping relationship. If the relation table is introduced when designing the relational database, the relation module table connecting two tables usually has a combined primary key (KEY1, KEY2), wherein KEY1 and KEY2 correspond to the two ends of the relation respectively.

[0033] Generally, the relationship data that stores the relationship between the ordinary tables does not exist in the relational database, but comes from the heterogeneous database, such as the heterogeneous relational database, the graph database, etc., or even from the external CSV file. We use external tables to make a layer of mapping to the relationship table inside the relational database.

[0034] The materialized adjacency index is a special index that is used to identify the connection between different records (points) in two tables. The connection relationship (edge) between the two tables is stored in the index, and a RowID partner pair is used to identify a specific relationship (edge).

[0035] The perspective of the graph model: the RowID of the ordinary table corresponds to the point in the graph model, and the record in the materialized adjacency index is the edge in the graph model, and each edge is marked by RowID->RowID. The adjacency index is used for adjacency query, and the query of adjacent points is performed by combining the index [RowID, RowID].

[0036] The adjacency index can use B-tree, B+ tree or LSM tree to organize data. For the KEY similar to the sorting index, the KEY is stored together, which can reduce the number of IO times and improve the query efficiency.

[0037] The materialized adjacency index, as the name implies, is not updated in real time, and is materialized when the index is created. The materialized data may come from the relationship table inside the relational database, such as:

[0038] create materialization adjacent index ma_index on

[0039] (select Teacher.RowID,Student.RowID

[0040] from Teacher t,Teach,Student s

[0041] Where t.id=Teach.teacher_id and s.id=Teach.student_id);

[0042] A simple model of teaching students by teachers in the course, a three-table join query, the Teacher-Key in the relational table is converted into the RowID in the Teacher table, and the Student-Key is also converted into the RowID in the Student table. The Teacher-Rowid and Student-Rowid form an adjacency index, and the result set of the query is "materialized" into the adjacency index ma_index. The adjacency data can be updated by periodically rebuilding the materialized adjacency index, that is, the purpose of updating the edge data is achieved.

[0043] Similar to the above technical path, the relationship data of the mapped external table can be directly "materialized" into the adjacency index.

[0044] A typical scenario is the department distribution of students taught by a teacher. A typical three-table join query is needed to connect to the Teach table through the teacher, and then to the Student table through the Teach table, and back to the Student table to calculate the department grouping.

[0045] With the help of materialized adjacency index query, the Rowid is located through the Teacher table, and then scanning can be directly performed on the materialized adjacency index to achieve the purpose of retrieving the taught students, and further back to the Student table to calculate the department grouping. In this process, the join calculation operator in the relational database is not used, but the adjacency query is completed by direct retrieval. In other words, the strategy of space for time is adopted to reduce the calculation amount, thereby improving the retrieval efficiency.

[0046] Regarding the processing of incremental data, there are multiple strategies to choose from:

[0047] Optionally, for transactional applications, whether there is incremental data is identified according to statistical information, and if there is incremental data, it is loaded into memory, and then the processing of incremental data is added when calculating. If the incremental data is too much, the materialized adjacency index is rebuilt to update the materialized adjacency index data, and then the query plan is re-executed.

[0048] Optionally, for analytical applications, the data of the incremental adjacency index can be ignored to achieve efficient access; at the same time, the user can specify the association query of the incremental data to obtain more accurate analysis results.

[0049] With the help of materialized adjacency index, multi-hop adjacency query can be realized, so that the relational database has the ability of fast adjacency query. For example, a seven-table join query can be used to make adjacency queries between three materialized adjacency indexes instead of six-table join queries, and the execution efficiency is improved by more than one order of magnitude.

[0050] The adjacency query like Figure 1 is a seven-table join query of Teacher, Student, Project and Budget through the Teach, Work_for and Obtain relationship tables. The query can be converted into a Teach, Work_for and Obtain adjacency query based on three materialized adjacency indexes. In the query process, the materialized adjacency indexes and their incremental data are used to realize efficient and fast adjacency queries, thereby replacing the original seven-table join mode.

[0051] In order to facilitate the understanding of the following embodiments, the following model is introduced. The entity table includes teachers, students, departments, projects, budgets and the like, and the relationship table includes the teaching (Teach) associated teachers and students, the participation (Work_for) associated students and projects, the obtaining (Obtain) associated projects and budgets, and the belonging (part_of) associated students and departments, thereby constructing a simple model.

[0052] Embodiment 1

[0053] This embodiment mainly shows the process of creating a materialized adjacency index, and the specific steps are as follows:

[0054] S101: If the relationship data (i.e. edge data) comes from a heterogeneous database or other data (including data files), external table mapping is needed to map into the relationship table in the ER model; otherwise, directly enter S102.

[0055] S102: The user writes a query statement of the materialized adjacency index based on the relationship table or the externally mapped relationship table, and initiates the SQL of creating the materialized adjacency index.

[0056] S103: The server obtains the data of the left row (starting point) and the right point (terminating point) in the materialized adjacency index by executing the query statement associated with the materialized adjacency index.

[0057] S104: The data of the left row (starting point) and the right point (terminating point) obtained by the query in S103 is materialized into the adjacency index.

[0058] S105: Update the data dictionary, insert a record of the materialized adjacency index belonging to the schema (library), and update it to the data dictionary cache to complete the creation of the materialized adjacency index.

[0059] Embodiment 2

[0060] This embodiment mainly shows how to use the materialized adjacency index in the query process, and the specific steps are as follows:

[0061] S201: The user queries the department distribution of the students taught by the teacher, and the SQL statement for connecting three tables is edited and sent to the database server, as follows:

[0062]

[0063] S202: The optimizer finds that there is an adjacency relationship, i.e., a graph adjacency scenario of teacher-teaching-student, and there is a materialized adjacency index, so the materialized adjacency index is preferentially selected (the original execution path locates the target teacher, joins the teaching according to the teacher number, then joins the student according to the student number, and finally performs aggregation and sorting output).

[0064] S203: The materialized adjacency index is directly retrieved to obtain the student IDs taught by all teachers. According to the user's specified whether to process incremental data, the default is not to process incremental data. If incremental data needs to be processed, the adjacency index is retrieved, and the data validity is detected from the incremental data cache. If the data has been deleted, the current record is skipped, and then S204 is entered.

[0065] The optimizer can be prompted by a hint whether to process incremental data; or whether to process incremental data can be controlled by a server parameter.

[0066] S204: When the materialized adjacency index is processed, the newly added adjacency (edge) data records in the incremental data need to be processed.

[0067] S205: According to the ID retrieved in S203 (incremental data needs to be processed, including S204), the table is backfilled to obtain the student's information, and subsequent aggregation calculation is performed.

[0068] Embodiment 3

[0069] This embodiment shows the conversion of a multi-table connection scenario into an adjacency query between multiple materialized adjacency indexes, thereby reducing the number of connections and improving query performance.

[0070] Background: The user needs to query the budget scale of the indirect association between teachers to evaluate the influence of the teacher. This query involves the students taught by the teacher, the projects participated by the student, and the budget obtained by the project. Through this scenario, this embodiment demonstrates how to optimize a multi-table connection query based on a materialized adjacency index. The specific steps are as follows:

[0071] S301: The user writes the budget scale of the indirect association between teachers to observe the influence of the professor, and the SQL statement is as follows:

[0072]

[0073] S302: The server parses the statement in S301 and preliminarily analyzes that it is a seven-table query, in which a materialized adjacency index exists. Teach, Work_for, and Obtain can connect teachers, students, projects, and budgets. Therefore, the adjacency query based on the materialized adjacency index will be prioritized.

[0074] Typically, a relational database, similar to that written in SQL, requires six join operations to reach the target budget table, and group calculations are performed to obtain the teacher's influence. Example 3 provides a processing strategy based on materialized adjacency indexes. Regarding the processing of incremental data, refer to Example 2.

[0075] S303: According to the execution plan generated in S302, perform Teach adjacency query, Work_for adjacency query and Obtain adjacency query in sequence to obtain the ID of the budget involved.

[0076] If you design an entity table for an intermediate path in a query, you might need to perform a table return operation for the intermediate entity, but this will not affect overall performance, as relational databases have similar requirements. However, in our hypothetical scenario, table return operations are unnecessary, which provides a more significant advantage.

[0077] S304: Based on the budget ID obtained in step S303, the summary of the budget items involved is calculated back in the table, sorted, and returned to the client.

[0078] In summary, with the help of materialization technology, the present invention enables relational data to have the edge storage capability in graph technology, and thus has the function of executing adjacency queries; secondly, through materialized adjacency indexing, multi-table connection queries are optimized, and the strategy of trading space for time is adopted to effectively accelerate the efficiency of multi-table connection queries; finally, the relational database is enabled with multi-hop adjacency query capabilities, and the relational data graph index function is given, thereby expanding the relational database's support for graph modality.

Claims

1. A method for implementing a materialized adjacency index in a relational database, characterized by: include: In the relational database, a materialized adjacency index is introduced to identify the relationship between different records in two tables. The materialized adjacency index stores the connection relationship between the two tables and identifies a specific relationship through a RowID partner pair. The materialized adjacency index is created by following the steps below: Create an adjacency index and use it to structure the data. Use the adjacency index to perform adjacency relationship query and obtain the adjacency data set; The adjacency dataset is materialized into the adjacency index, and the records in the materialized adjacency index are converted into edges in the graph model. Each edge is marked by RowID->RowID.

2. The method for implementing a materialized adjacency index for a relational database according to claim 1, wherein: Update adjacency data by periodically rebuilding the materialized adjacency index.

3. The method for implementing a materialized adjacency index for a relational database according to claim 1, wherein: The structured organization of data by using the adjacency index specifically includes: the adjacency index adopts B-tree, B+ tree or LSM tree to structure the data.

4. The method for implementing a materialized adjacency index for a relational database according to claim 1, wherein: The method of using the adjacency index to perform adjacency query to obtain the adjacency data set specifically includes: performing adjacency query through the combined index [RowID, RowID] to obtain the adjacency data set.

5. The method for implementing a materialized adjacency index for a relational database according to claim 1, wherein: When performing an adjacency query on the relational data in the external table, the relational data in the external table is first uniformly mapped to the relational table inside the relational database, and then the adjacency query is performed.

6. A query method based on a materialized adjacency index of a relational database, characterized by: include: According to the method for implementing a materialized adjacency index in a relational database according to any one of claims 1 to 5, a materialized adjacency index is introduced into the relational database; By locating the RowID of a known table, the query result is obtained by directly scanning the materialized adjacency index.

7. The query method based on the materialized adjacency index of a relational database according to claim 6, characterized in that: New adjacency relationships are handled according to the following policies: For transactional applications, when the number of newly added adjacency relationships does not exceed the set value, they are first loaded into memory and then processed. When the number of newly added adjacency relationships exceeds the set value, the materialized adjacency index data is updated by rebuilding the materialized adjacency index. For analytical applications, newly added adjacency relationships are ignored, and users are supported to specify associated queries for newly added adjacency relationships.

Images