Management methods and devices for relational databases

Abstract

This application provides one or more embodiments of a relational database management method and apparatus. The method includes: obtaining a creation instruction for creating a target data table in the relational database; wherein the creation instruction includes column information corresponding to basic data columns in the target data table; responding to the creation instruction, creating the target data table in the relational database, including the basic data columns and hidden data columns, based on the column information; wherein the hidden data columns are extended data columns independent of the basic data columns; after the target data table is created, establishing a unique tree-structured index for the target data table based on the data in the hidden data columns; wherein the data in the hidden data columns corresponding to data rows in the target data table includes monotonically increasing values.

Description

Technical Field

[0001] One or more embodiments of this application relate to the field of database technology, and in particular to a method and apparatus for managing relational databases. Background Technology

[0002] A relational database is a database that uses a relational model to organize data, typically storing data in rows and columns. In a relational database, a series of rows and columns of data is called a data table, and a set of data tables makes up the database. Generally, a row of data in a data table is a data record, and a column of data corresponds to a field.

[0003] For relational database tables, if the volume of data being manipulated is large and requires joins with other tables, or if the data is temporary and requires numerous DML (Data Manipulation Language) operations in a short period, or if multi-table joins are needed, directly manipulating the original data table is inefficient. In these scenarios, temporary data tables are typically used to improve efficiency. As the name suggests, a temporary data table is a table that is used temporarily and then destroyed.

[0004] However, while temporary data tables offer some convenience, the frequent DML operations such as inserts and deletes performed on them can lead to unstable query performance and long query times. Therefore, optimizing queries on temporary data tables has become a pressing issue. Summary of the Invention

[0005] One or more embodiments of this application provide the following technical solutions:

[0006] This application provides a method for managing a relational database, the method comprising:

[0007] Obtain a creation instruction for creating a target data table in a relational database; wherein the creation instruction includes column information corresponding to the basic data columns in the target data table;

[0008] In response to the creation instruction, based on the column information, a target data table including the basic data columns and hidden data columns is created in the relational database; wherein, the hidden data columns are extended data columns independent of the basic data columns;

[0009] After the target data table is created, a unique tree-structured index is built for the target data table based on the data in the hidden data column; wherein, the data in the hidden data column corresponding to the data rows in the target data table includes monotonically increasing values.

[0010] This application also provides a query method for a relational database, the method comprising:

[0011] Obtain a query instruction for performing a full table scan on a target data table; wherein, the target data table is a data table created in the relational database in response to a creation instruction for creating a target data table in the relational database, based on column information corresponding to the basic data columns in the target data table included in the creation instruction; the hidden data columns are extended data columns independent of the basic data columns; after the target data table is created, a unique tree-structured index is established for the target data table based on the data in the hidden data columns;

[0012] In response to the query instruction, query conditions corresponding to the hidden data column are added to the query instruction to update the query instruction;

[0013] The updated query instruction is executed to query the target data table based on the unique index of the tree structure, so as to retrieve the data of each data row record in the target data table.

[0014] This application also provides a management device for a relational database, the device comprising:

[0015] The first acquisition module acquires a creation instruction for creating a target data table in a relational database; wherein the creation instruction includes column information corresponding to the basic data columns in the target data table;

[0016] The table creation module, in response to the creation instruction, creates the target data table in the relational database based on the column information, including the basic data columns and hidden data columns; wherein, the hidden data columns are extended data columns independent of the basic data columns;

[0017] The index building module, after the target data table is created, builds a unique tree-structured index for the target data table based on the data in the hidden data column; wherein, the data in the hidden data column corresponding to the data rows in the target data table includes monotonically increasing values.

[0018] This application also provides a query apparatus for a relational database, the apparatus comprising:

[0019] The second acquisition module acquires a query instruction for performing a full table scan of the target data table; wherein, the target data table is a data table created in the relational database in response to a creation instruction for creating a target data table in the relational database, based on column information corresponding to the basic data columns in the target data table included in the creation instruction; the hidden data columns are extended data columns independent of the basic data columns; after the target data table is created, a unique tree-structured index is established for the target data table based on the data in the hidden data columns;

[0020] The update module, in response to the query instruction, adds query conditions corresponding to the hidden data column to the query instruction to update the query instruction;

[0021] The query module executes the updated query instruction and queries the target data table based on the unique index of the tree structure to retrieve the data of each data row record in the target data table.

[0022] This application also provides an electronic device, including:

[0023] processor;

[0024] Memory used to store processor-executable instructions;

[0025] The processor executes the executable instructions to implement the steps of the method as described in any of the preceding descriptions.

[0026] This application also provides a computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method as described in any of the preceding claims.

[0027] In the above technical solution, when a creation instruction for creating a data table in a relational database is obtained, a data table can be created in the relational database based on the column information corresponding to the basic data column in the data table to be created, including the basic data column and the extended hidden data column independent of the basic data column. Based on the values ​​in the hidden data column corresponding to the data rows in the data table, a unique index of tree structure is established for the data table, so that the data table can be queried subsequently based on the unique index of tree structure.

[0028] Using the above method, when creating a tree-structured index for a table in a relational database, the data selected is not from the basic data columns of the table. Instead, the data in the basic data columns is typically generated during the actual operation of devices or software services, and the dispersion of this data is uncontrolled. Therefore, this avoids the problem of data dispersion in the tree index, which could affect the query performance and query time of the table. Correspondingly, by adding a hidden data column to the table and building the tree-structured index based on the monotonically increasing values ​​in this hidden data column corresponding to the data rows in the table, the dispersion of the data used to build the tree index is controlled, thereby improving the query performance and reducing the corresponding query time. Furthermore, an index built using monotonically increasing values ​​can be a unique index, thus ensuring the accuracy of queries based on this unique index. Attached Figure Description

[0029] Figure 1 This is a schematic diagram of a B-Tree shown in an exemplary embodiment of this application.

[0030] Figure 2 This is a flowchart illustrating a relational database management method according to an exemplary embodiment of this application.

[0031] Figure 3 This is a schematic diagram of another B-Tree shown in an exemplary embodiment of this application.

[0032] Figure 4 This is a flowchart illustrating a query method for a relational database according to an exemplary embodiment of this application.

[0033] Figure 5 This is a schematic diagram of the hardware structure of a device shown in an exemplary embodiment of this application.

[0034] Figure 6 This is a block diagram illustrating a relational database management device according to an exemplary embodiment of this application.

[0035] Figure 7 This is a block diagram illustrating a query apparatus for a relational database according to an exemplary embodiment of this application. Detailed Implementation

[0036] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with one or more embodiments of this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of one or more embodiments of this application as detailed in the appended claims.

[0037] It should be noted that the steps of the corresponding methods in other embodiments are not necessarily performed in the order shown and described in this application. In some other embodiments, the methods may include more or fewer steps than those described in this application. Furthermore, a single step described in this application may be broken down into multiple steps in other embodiments; and multiple steps described in this application may be combined into a single step in other embodiments.

[0038] Before providing a detailed description of one or more embodiments of this application, a brief explanation of the application scenarios involved in one or more embodiments of this application will be given first.

[0039] A database system typically includes a database (DB), hardware, and software. A database is a large, organized, shareable, and centrally managed collection of data that is permanently stored in a computer. The data in the database is organized, described, and stored according to a specific mathematical model. Hardware comprises the various physical devices that make up the computer system, including external devices required for storage. The hardware configuration should meet the needs of the entire database system. Software can further include the operating system, the Database Management System (DBMS), and application programs. The DBMS is the core software of the database system. It operates with the support of the operating system and addresses how to scientifically organize and store data, and how to efficiently acquire and maintain data. Its main functions include: data definition, data manipulation, database operation and management, and database creation and maintenance.

[0040] In database systems, database management systems typically use storage engines to perform DDL (Data Definition Language) and DML operations on the database. A storage engine is the implementation method for how data is stored, how indexes are created, and how data is queried and updated. A database system usually supports multiple storage engines to meet the needs of different database application scenarios.

[0041] A relational database system is a database system that includes relational databases. In relational database systems, temporary tables are typically not serialized to disk but reside in memory and are destroyed after use. Temporary tables can be divided into two types: session-level temporary tables and transaction-level temporary tables. Session-level temporary tables exist throughout the entire session until it ends; transaction-level temporary tables disappear after the transaction ends—that is, they are cleared after a transaction is committed or rolled back, or when the session ends. In most cases, DML operations such as inserts and deletes frequently occur in temporary tables.

[0042] An index is a data structure that helps relational database systems retrieve data efficiently. In a relational database system, an index can be used to sort one or more columns of data in a table, thereby enabling quick retrieval of specific data within the table. An index consists of a set of one or more columns of data in a table, along with a corresponding list of logical pointers that physically identify the data pages in the table. In other words, an index provides pointers to data stored in specific columns of a table, allowing you to find specific data using the index and then locate the row containing that data using the corresponding pointers.

[0043] In practical applications, a relational database system typically supports multiple storage engines, and each storage engine usually supports multiple indexes, resulting in a single relational database system supporting various indexes, such as B-Tree indexes, hash indexes, and full-text indexes. Currently, most database systems and file systems use B-Tree or its variant B+Tree as their index data structure. A B-Tree is a balanced multi-way search tree, a tree structure with high dynamic search efficiency, suitable for storage systems such as database systems and file systems that read and write relatively large data blocks.

[0044] To describe a B-Tree, we first define a data record as a tuple [key, data]. Here, key is the key value of the data record; data is all the data in the record except for key. For a B-Tree index, a tuple [key, data] represents a row of data, where key is the data in the column used to create the index, and data is all the data in the row except for the data in the column used to create the index. In this case, a B-Tree can be a data structure that at least satisfies the following conditions:

[0045] 1. m is a positive integer greater than 1, which is called the order of the B-Tree, or the degree of the B-Tree.

[0046] 2. A node has at most m child nodes, the root node has at least 2 child nodes, and a non-root and non-leaf node has at least [m / 2] ([] represents rounding up).

[0047] 3. The number of keys in the root node is in the range of 1 to m-1, and the number of keys in non-root nodes is in the range of [m / 2]-1 to m-1.

[0048] 4. The keys in a node are arranged in a non-decreasing order from left to right.

[0049] 5. All keys in the nth child node from left to right of a node are less than the nth key from left to right in this node and greater than the (n-1)th key from left to right in this node.

[0050] For example, suppose the data tables in a relational database are as shown in Table 1 below:

[0051]

[0052]

[0053] Table 1

[0054] In this column, ID1 is the name of the first column, and X1-X15 represent the data in the first column; ID2 is the name of the second column, and Y1-Y15 represent the data in the second column; ID3 is the name of the third column. X1-X15 and Y1-Y15 can be the same or different values.

[0055] For the data table shown in Table 1 above, if the data in the third column is used as the key in the B-Tree index, the created 3rd-order B-Tree index will be as follows: Figure 1 As shown. It should be noted that, as... Figure 1 The B-Tree index shown only displays the keys in each node, and does not show other data.

[0056] The main difference between B+Tree and B-Tree lies in the upper limit of the number of child nodes a node can have, and in B+Tree, non-leaf nodes do not store data but only keys.

[0057] In practical applications, there are situations where a full table scan of a relational database table is necessary. If the table does not have an index on the columns corresponding to the data used in the query, then a full table scan is required. Similarly, a full table scan is also necessary if a query is performed on all columns of the table, meaning all rows and columns are retrieved.

[0058] Taking the data table shown in Table 1 above as an example, suppose we want to query all the data in the data rows corresponding to the two data points Y6 and Y7. Since the index of this data table is built based on the data in the third column, and the data used for querying Y6 and Y7 are the data in the second column, we need to perform a full table scan to get all the data in the data rows corresponding to the two data points Y6 and Y7.

[0059] However, for relational database systems that use B-Tree or B+Tree as their in-memory storage engine, if the data in the indexed columns of the temporary data table is discrete, or if the data in the indexed columns of the temporary data table becomes discrete due to frequent insertions or deletions of data rows, then the query performance for the temporary data table will be unstable and time-consuming.

[0060] Taking the data table shown in Table 1 above as an example, the data in the third column of this data table is relatively discrete. This can result in the data in the 10th and 11th rows being stored on two completely different branches in the B-Tree index: 100-120-110 and 100-20-5. In other words, the nodes corresponding to adjacent data rows in the B-Tree index are relatively scattered, which affects the query performance and query time for temporary data tables.

[0061] This application proposes a technical solution for managing relational databases to optimize queries on data tables within the relational database. In this solution, upon receiving a creation instruction to create a data table in the relational database, a data table can be created based on the column information corresponding to the basic data columns in the creation instruction. This table includes the basic data columns and extended hidden data columns independent of the basic data columns. Furthermore, a unique tree-structured index is established for the data table based on the values ​​in the hidden data columns corresponding to the data rows in the data table, enabling subsequent queries on the data table based on this unique tree-structured index.

[0062] In practical implementation, the first step is to obtain the instructions (referred to as creation instructions) used to create a data table (called the target data table) in the relational database. These creation instructions can include column information, which can correspond to the basic data columns in the target data table.

[0063] Upon receiving the aforementioned creation instruction, the system can further respond to the instruction by creating the target data table in the relational database based on the column information provided. In this case, the created target data table may include not only the basic data columns corresponding to the specified column information, but also hidden data columns. These hidden data columns are extended data columns independent of the basic data columns.

[0064] After the target data table is created, since it includes the hidden data columns, a tree-structured unique index can be built for the target data table based on the data in the hidden data columns. The data in the hidden data columns corresponding to the data rows in the target data table can include monotonically increasing values.

[0065] Using the above method, when creating a tree-structured index for a table in a relational database, the data selected is not from the basic data columns of the table. Instead, the data in the basic data columns is typically generated during the actual operation of devices or software services, and the dispersion of this data is uncontrolled. Therefore, this avoids the problem of data dispersion in the tree index, which could affect the query performance and query time of the table. Correspondingly, by adding a hidden data column to the table and building the tree-structured index based on the monotonically increasing values ​​in this hidden data column corresponding to the data rows in the table, the dispersion of the data used to build the tree index is controlled, thereby improving the query performance and reducing the corresponding query time. Furthermore, an index built using monotonically increasing values ​​can be a unique index, thus ensuring the accuracy of queries based on this unique index.

[0066] Please refer to Figure 2 , Figure 2 This is a flowchart illustrating a relational database management method according to an exemplary embodiment of this application.

[0067] For relational database systems, the aforementioned management methods can be used to manage the relational databases within them, such as creating data tables. In this case, these management methods can be applied to the database management system (DBMS) within the relational database system, which then manages the relational databases within the system.

[0068] In some embodiments, the relational database described above may include OceanBase, which is a distributed relational database.

[0069] The above-mentioned methods for managing relational databases may include the following steps:

[0070] Step 202: Obtain the creation instruction for creating the target data table in the relational database; wherein the creation instruction includes column information corresponding to the basic data columns in the target data table.

[0071] In this embodiment, an instruction (referred to as a creation instruction) for creating a data table (referred to as the target data table) in a relational database can first be obtained. The creation instruction may include column information, which may correspond to the basic data columns in the target data table.

[0072] In practical applications, the column information corresponding to a data column can include the column name of the data column, as well as the data type of the data in the data column.

[0073] For example, the DDL statement used to create the target data table in the relational database can be obtained and the DDL statement can be translated into processor-executable creation instructions.

[0074] Take the following DDL statement as an example:

[0075] create table tmp_test(id1 int,id2 int)

[0076] This DDL statement indicates that a table named tmp_test should be created in a relational database. The table will have two columns, named id1 and id2, with all data of type int. In this case, the DDL statement can be translated into processor-executable instructions, allowing the processor to create the table in the relational database by executing these instructions.

[0077] In this example, the target data table created in the relational database is named tmp_test, id1 and int are the column information corresponding to the first basic data column in the target data table, and id2 and int are the column information corresponding to the second basic data column in the target data table.

[0078] In some embodiments, the target data table may include a data table in which insert and / or delete operations are performed multiple times in a short period of time, i.e., a data table in which DML operations such as insert and delete are frequently performed.

[0079] Furthermore, in some embodiments, the aforementioned target data table may specifically be a temporary data table.

[0080] Step 204: In response to the creation instruction, based on the column information, create the target data table in the relational database, which includes the basic data column and the hidden data column; wherein the hidden data column is an extended data column independent of the basic data column.

[0081] In this embodiment, upon receiving the aforementioned creation instruction, the system can further respond to the creation instruction and create the target data table in the relational database based on the column information in the creation instruction. At this time, the created target data table may include not only the basic data columns corresponding to the column information but also hidden data columns. These hidden data columns are extended data columns independent of the basic data columns.

[0082] It should be noted that both the basic data columns and the hidden data columns mentioned above are data columns in a relational database table. The difference between the basic data columns and the hidden data columns lies in whether the creation instruction obtained first includes the corresponding column information.

[0083] In some embodiments, the target data table can be created in the relational database using any of the following different methods:

[0084] 1. In response to the aforementioned creation command, column information corresponding to the hidden data columns can be added to the creation command to update it. That is, the updated creation command includes not only the column information corresponding to the basic data columns but also the column information corresponding to the hidden data columns. Subsequently, the updated creation command can be executed, and based on these two types of column information in the updated creation command, the target data table, including the basic data columns and the hidden data columns, can be created in the relational database.

[0085] Continuing with the above DDL statement as an example, assuming the hidden data column is pre-configured as `global_trx_idx` and the data type of the data in the hidden data column is configured as `varchar(512)`, i.e., a variable-length string with a maximum length of 512 characters, then `global_trx_idx` and `varchar(512)` are the column information corresponding to the hidden data column. In this case, the column information corresponding to the hidden data column can be added to the DDL statement to update it. The updated DDL statement can be as follows:

[0086] create table tmp_test(global_trx_idx varchar(512),id1 int,id2 int)

[0087] In this example, the target data table ultimately created in the relational database includes three columns: a hidden data column containing data of type varchar(512), a first basic data column containing data of type int, and a second basic data column containing data of type int.

[0088] 2: The above creation command can be executed to first obtain the column information corresponding to the basic data columns in the creation command, and obtain the column information corresponding to the hidden data columns. Then, based on the two types of column information obtained, the target data table including the basic data columns and hidden data columns can be created in the relational database.

[0089] 3: The above creation command can be executed to first create an initial target data table in the relational database that only includes the basic data columns, based on the column information corresponding to the basic data columns in the creation command. Then, based on the column information corresponding to the hidden data columns, hidden data columns are added to the initial target data table to obtain a final target data table that includes both basic data columns and hidden data columns.

[0090] Step 206: After the target data table is created, a unique tree-structured index is established for the target data table based on the data in the hidden data column; wherein, the data in the hidden data column corresponding to all data rows in the target data table may include monotonically increasing values.

[0091] In this embodiment, after the target data table is created, since the target data table includes the aforementioned hidden data columns, a unique tree-structured index can be established for the target data table based on the data in the hidden data columns. The data in the hidden data columns corresponding to the data rows in the target data table can include monotonically increasing values.

[0092] In other words, it's equivalent to executing the following additional DDL statement:

[0093] create unique index u_global_trx_idx on tmp_test(global_trx_idx)

[0094] This DDL statement indicates that in a relational database, for the table named tmp_test, a unique index will be created based on the data in the column named global_trx_idx. If the corresponding relational database system uses a tree-structured in-memory storage engine, then the created unique index will also be a tree-structured unique index.

[0095] For example, suppose the target data table is shown in Table 2 below:

[0096] global_trx_idx id1 id2 X1 Y1 Z1 X2 Y2 Z2 X3 Y3 Z3

[0097] Table 2

[0098] The table is structured as follows: the first column is the hidden data column, named `global_trx_idx`, where X1-X3 represent the data in the hidden data column; the second column is the first basic data column, named `id1`, where Y1-Y3 represent the data in the first basic data column; and the third column is the second basic data column, named `id2`, where Z1-Z3 represent the data in the second basic data column. In this case, X1 corresponds to the data in the hidden data column that corresponds to the first row in the target data table, X2 corresponds to the data in the hidden data column that corresponds to the data in the second row in the target data table, and X3 corresponds to the data in the hidden data column that corresponds to the data in the third row in the target data table. X1, X2, and X3 can be three monotonically increasing values. For example: X1 = 1, X2 = 2, X3 = 3; or X1 = 1, X2 = 3, X3 = 5; and so on.

[0099] In practical applications, unique indexes do not allow data rows with the same key value. This means that the data in the column used to create the unique index must be different for each different data row, thus preventing duplicate data in the unique index. Since the data in the aforementioned hidden column includes monotonically increasing values, these values ​​must be distinct. Therefore, an index built on the hidden column for the target table can be a unique index.

[0100] In some embodiments, the unique index of the tree structure described above may include a unique index based on a B-Tree or a B+Tree.

[0101] For example, assuming the target data table contains 21 rows, the hidden data column in this table contains 21 monotonically increasing values. Further, assuming these 21 monotonically increasing values ​​are 1-21, the unique index built on a 4-order B-Tree for this data table based on the data in the hidden data column would be as follows: Figure 3 As shown. It should be noted that, as... Figure 2The B-Tree index shown only displays the keys in each node, and does not show other data.

[0102] As Figure 2 As shown in the B-Tree index, when the data used to build the B-Tree index is a monotonically increasing value, the nodes corresponding to adjacent data rows in the B-Tree index are relatively clustered. For example, the data of the two adjacent rows, rows 1 and 2, are stored on the two branches 10-3-1 and 10-3-2 in the B-Tree index. Except for the leaf nodes, the other nodes on these two branches are completely identical.

[0103] In some embodiments, the data in the hidden data column corresponding to all data rows in the target data table may include a tuple. This tuple may include the identifier of the device that generated the data in each data row corresponding to the underlying data column, and a numerical value. For example, the tuple may be represented as: [svr_id, local_inc_num], where svr_id represents the identifier of the device that generated the data in each data row corresponding to the underlying data column, and local_inc_num represents a monotonically increasing numerical value.

[0104] Taking the first data row of the target data table shown in Table 2 above as an example, X1 is the data in this data row corresponding to the aforementioned hidden data column, Y1 is the data in this data row corresponding to the aforementioned first basic data column, and Z1 is the data in this data row corresponding to the aforementioned second basic data column. Specifically, X1 can be [svr_id1, local_inc_num1], where svr_id1 represents the identifier of the device that generated Y1 and Z1 in this data row, and local_inc_num1 represents a numerical value. Similarly, X2 can be [svr_id2, local_inc_num2], where svr_id2 represents the identifier of the device that generated Y2 and Z2 in the second data row, and local_inc_num2 represents a numerical value; X3 can be [svr_id3, local_inc_num3], where svr_id3 represents the identifier of the device that generated Y3 and Z3 in the third data row, and local_inc_num3 represents a numerical value. At this point, local_inc_num1, local_inc_num2, and local_inc_num3 are three monotonically increasing values.

[0105] It should be noted that the values ​​in a tuple containing identifiers of the same device can be monotonically increasing, but the values ​​in a tuple containing identifiers of different devices can be repeated.

[0106] Continuing with the example of the above tuple, for the same device, the value represented by local_inc_num is monotonically increasing; while for different devices, the value represented by local_inc_num can be repeated. In this case, since svr_id is different, it can still be guaranteed that the tuple is not repeated.

[0107] In practical applications, to ensure that the newly added hidden data columns in the target data table are not duplicated after the device crashes and restarts, the maximum value of the monotonically increasing values ​​can be periodically persisted to ensure that the value does not revert after the device crashes and restarts.

[0108] Taking the above tuple as an example, for the same device, the maximum value in local_inc_num can be periodically persisted to ensure that local_inc_num does not roll back after the device crashes and restarts.

[0109] In some embodiments, the identifier of the device includes the device's IP address. In this case, the tuple can be represented as: [svr_ip, local_inc_num]. Where svr_ip represents the device's IP address.

[0110] Alternatively, in some embodiments, the device identifier includes the device's IP address and port number. In this case, the tuple can be represented as: [svr_ip / svr_port, local_inc_num]. Here, svr_ip represents the device's IP address, and svr_port represents the device's port number.

[0111] In the above technical solution, when a creation instruction for creating a data table in a relational database is obtained, a data table can be created in the relational database based on the column information corresponding to the basic data column in the data table to be created, including the basic data column and the extended hidden data column independent of the basic data column. Based on the values ​​in the hidden data column corresponding to the data rows in the data table, a unique index of tree structure is established for the data table, so that the data table can be queried subsequently based on the unique index of tree structure.

[0112] Using the above method, when creating a tree-structured index for a table in a relational database, the data selected is not from the basic data columns of the table. Instead, the data in the basic data columns is typically generated during the actual operation of devices or software services, and the dispersion of this data is uncontrolled. Therefore, this avoids the problem of data dispersion in the tree index, which could affect the query performance and query time of the table. Correspondingly, by adding a hidden data column to the table and building the tree-structured index based on the monotonically increasing values ​​in this hidden data column corresponding to the data rows in the table, the dispersion of the data used to build the tree index is controlled, thereby improving the query performance and reducing the corresponding query time. Furthermore, an index built using monotonically increasing values ​​can be a unique index, thus ensuring the accuracy of queries based on this unique index.

[0113] Please combine Figure 2 ,refer to Figure 4 , Figure 4 This application illustrates an exemplary embodiment of a relational database query method.

[0114] For relational database systems, the aforementioned relational database query methods can be used to query the relational databases within them. In this case, these relational database query methods can be applied to the database management system within the relational database system, allowing the database management system to perform queries on the relational databases within the system.

[0115] The query method for relational databases described above may include the following steps:

[0116] Step 402: Obtain a query instruction for performing a full table scan on the target data table; wherein, the target data table is a data table created in the relational database in response to a creation instruction for creating a target data table in the relational database, based on column information corresponding to the basic data columns in the target data table included in the creation instruction; the hidden data columns are extended data columns independent of the basic data columns; after the target data table is created, a unique tree-structured index is established for the target data table based on the data in the hidden data columns.

[0117] In this embodiment, the specific method for creating a data table and establishing a unique tree-structured index for the data table can be referred to as follows: Figure 2 The embodiments shown are not described in detail here.

[0118] When querying the target data table mentioned above, the first step is to obtain the instruction (called the query instruction) for performing a full table scan on the target data table.

[0119] For example, it is possible to obtain the DDL statement that performs a full table scan on the target data table and convert the DDL statement into a processor-executable creation instruction.

[0120] Take the following DDL statement as an example:

[0121] select * from tmp_test

[0122] This DDL statement indicates a full table scan of the table named tmp_test in a relational database. In this case, the DDL statement can be translated into processor-executable instructions, allowing the processor to perform a full table scan of the table by executing these instructions.

[0123] Step 404: In response to the query instruction, add query conditions corresponding to the hidden data column to the query instruction to update the query instruction.

[0124] In this embodiment, upon receiving the aforementioned query instruction, the system can further respond to the query instruction by adding query conditions corresponding to the aforementioned hidden data column to update the query instruction.

[0125] Continuing with the DDL statement example above, assuming the hidden data column is named global_trx_idx, we can add query conditions corresponding to the hidden data column to update the DDL statement. The updated DDL statement can then be shown below:

[0126] select*from tmp_test where global_trx_idx='xxx'

[0127] This DDL statement queries the table named tmp_test in a relational database based on the column named global_trx_idx. Since the column named global_trx_idx is the hidden column mentioned above, the WHERE clause for global_trx_idx = 'xxx' is the query condition corresponding to the hidden column.

[0128] Step 406: Execute the updated query instruction, and query the target data table based on the unique index of the tree structure to retrieve the data of each data row record in the target data table.

[0129] In this embodiment, the updated query instruction can be executed. Since the updated query instruction contains query conditions corresponding to the hidden data column, and a unique index of the tree structure is established for the target data table based on the data in the hidden data column, the target data table can be queried based on the unique index of the tree structure to query the data of each data row record in the target data table.

[0130] In the above technical solution, the query instruction used to perform a full table scan of the data table can be rewritten, transforming it into a new query instruction for querying the data table based on the unique index of the tree structure built for the data table. Thus, by executing the new query instruction, the query can be performed on the data table based on the unique index of the tree structure built for the data table, eliminating the need for a full table scan. This improves the query efficiency of full table scans in relational databases.

[0131] Please refer to Figure 5 , Figure 5 This is a schematic diagram of the hardware structure of a device shown in an exemplary embodiment of this application.

[0132] like Figure 5 As shown, at the hardware level, the aforementioned device includes a processor 502, an internal bus 504, a network interface 506, memory 508, and non-volatile memory 510, and may also include other hardware required for business operations. One or more embodiments of this application can be implemented in software, for example, the processor 502 reads the corresponding computer program from the non-volatile memory 510 into memory 508 and then runs it. Of course, in addition to software implementation, one or more embodiments of this application do not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution subject of the following processing flow is not limited to each logic module, but can also be hardware or logic devices.

[0133] Please refer to Figure 6 , Figure 6 This is a block diagram illustrating a relational database management device according to an exemplary embodiment of this application.

[0134] The aforementioned relational database management device can be applied to, for example... Figure 5 The device shown is used to implement the technical solution of this application. The management device for this relational database may include:

[0135] The first acquisition module 602 acquires a creation instruction for creating a target data table in a relational database; wherein the creation instruction includes column information corresponding to the basic data columns in the target data table;

[0136] Table creation module 604, in response to the creation instruction, creates the target data table in the relational database based on the column information, including the basic data columns and hidden data columns; wherein, the hidden data columns are extended data columns independent of the basic data columns;

[0137] The index building module 606, after the target data table is created, builds a unique tree-structured index for the target data table based on the data in the hidden data column; wherein, the data in the hidden data column corresponding to the data rows in the target data table includes monotonically increasing values.

[0138] Optionally, the table creation module 604 is specifically used for:

[0139] In response to the creation instruction, column information corresponding to the hidden data column is added to the creation instruction to update the creation instruction;

[0140] Execute the updated creation instruction, and based on the column information corresponding to the basic data column and the column information corresponding to the hidden data column in the updated creation instruction, create the target data table including the basic data column and the hidden data column in the relational database.

[0141] Optionally, the table creation module 604 is specifically used for:

[0142] Execute the creation instruction, obtain the column information corresponding to the basic data column in the creation instruction, and obtain the column information corresponding to the hidden data column;

[0143] Based on the column information corresponding to the basic data column and the column information corresponding to the hidden data column, a target data table including the basic data column and the hidden data column is created in the relational database.

[0144] Optionally, the table creation module 604 is specifically used for:

[0145] Execute the creation instruction, and based on the column information corresponding to the basic data column in the creation instruction, create the target data table including the basic data column in the relational database;

[0146] Based on the column information corresponding to the hidden data column, a hidden data column is added to the target data table to obtain the target data table including the basic data column and the hidden data column.

[0147] Optionally, the data in the hidden data column corresponding to the data row in the target data table includes tuples; wherein, the tuples include the identifier of the device that generated the data in the data row corresponding to the basic data column, and a value; the values ​​in tuples that include the identifier of the same device are monotonically increasing.

[0148] Optionally, the device identifier includes the device's IP address; or, the device identifier includes the device's IP address and port number.

[0149] Optionally, the unique index of the tree structure includes a unique index based on a B-Tree or a B+Tree.

[0150] Optionally, the target data table includes a data table in which insert and / or delete operations are performed multiple times within a short period of time.

[0151] Optionally, the target data table may include a temporary data table.

[0152] Optionally, the relational database includes OceanBase.

[0153] Please refer to Figure 7 , Figure 7 This is a block diagram illustrating a query apparatus for a relational database according to an exemplary embodiment of this application.

[0154] The aforementioned relational database management device can be applied to, for example... Figure 5 The apparatus shown is used to implement the technical solution of this application. The query device for this relational database may include:

[0155] The second acquisition module 702 acquires a query instruction for performing a full table scan on a target data table; wherein, the target data table is a data table created in the relational database in response to a creation instruction for creating a target data table in a relational database, based on column information corresponding to the basic data columns in the target data table included in the creation instruction; the hidden data columns are extended data columns independent of the basic data columns; after the target data table is created, a unique tree-structured index is established for the target data table based on the data in the hidden data columns;

[0156] The instruction update module 704, in response to the query instruction, adds query conditions corresponding to the hidden data column to the query instruction to update the query instruction;

[0157] The table query module 706 executes the updated query instruction and queries the target data table based on the unique index of the tree structure to retrieve the data of each data row record in the target data table.

[0158] Optionally, the data in the hidden data column corresponding to the data row in the target data table includes tuples; wherein, the tuples include the identifier of the device that generated the data in the data row corresponding to the basic data column, and a value; the values ​​in tuples that include the identifier of the same device are monotonically increasing.

[0159] Optionally, the device identifier includes the device's IP address; or, the device identifier includes the device's IP address and port number.

[0160] Optionally, the unique index of the tree structure includes a unique index based on a B-Tree or a B+Tree.

[0161] Optionally, the target data table includes a data table in which insert and / or delete operations are performed multiple times within a short period of time.

[0162] Optionally, the target data table may include a temporary data table.

[0163] Optionally, the relational database includes OceanBase.

[0164] The apparatus embodiments are basically the same as the method embodiments, so relevant details can be found in the description of the method embodiments.

[0165] The device embodiments described above are merely illustrative. The modules described as separate components may or may not be physically separate. The components shown as modules may or may not be physical modules; that is, they may be located in one place or distributed across multiple network modules. Some or all of the modules can be selected to achieve the purpose of the technical solution of this application according to actual needs.

[0166] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which can take the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email sending and receiving device, game console, tablet computer, wearable device, or any combination of these devices.

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

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

[0169] Computer-readable media include 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, disk storage, quantum memory, graphene-based storage media 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.

[0170] 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 a process, method, article, or apparatus. Without further limitation, 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 said element.

[0171] The foregoing has described specific embodiments of this application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired results. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0172] The terminology used in one or more embodiments of this application is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this application. The singular forms “a,” “the,” and “the” used in one or more embodiments of this application and in the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0173] It should be understood that although the terms first, second, third, etc., may be used to describe various information in one or more embodiments of this application, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, without departing from the scope of one or more embodiments of this application, first information may also be referred to as second information, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "when," or "in response to a determination."

[0174] The above description is merely a preferred embodiment of one or more embodiments of this application and is not intended to limit the scope of one or more embodiments of this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this application should be included within the protection scope of one or more embodiments of this application.

Claims

1. A method for managing a relational database, the method comprising: Obtain a creation instruction for creating a target data table in a relational database; wherein the creation instruction includes column information corresponding to the basic data columns in the target data table; In response to the creation instruction, based on the column information, a target data table including the basic data columns and hidden data columns is created in the relational database; wherein, the hidden data columns are extended data columns independent of the basic data columns; After the target data table is created, a unique tree-structured index is built for the target data table based on the data in the hidden data column. The data in the hidden data column corresponding to the data rows in the target data table includes monotonically increasing values. In the query instruction that performs a full table scan on the target data table, the query instruction is rewritten by adding query conditions corresponding to the hidden data column, so as to transform the query instruction into an instruction to query the target data table based on the unique index.

2. The method according to claim 1, wherein, in response to the creation instruction, creating the target data table in the relational database, based on the column information, comprising: In response to the creation instruction, column information corresponding to the hidden data column is added to the creation instruction to update the creation instruction; Execute the updated creation instruction, and based on the column information corresponding to the basic data column and the column information corresponding to the hidden data column in the updated creation instruction, create the target data table including the basic data column and the hidden data column in the relational database.

3. The method according to claim 1, wherein, in response to the creation instruction, creating the target data table in the relational database, based on the column information, comprising: Execute the creation instruction, obtain the column information corresponding to the basic data column in the creation instruction, and obtain the column information corresponding to the hidden data column; Based on the column information corresponding to the basic data column and the column information corresponding to the hidden data column, a target data table including the basic data column and the hidden data column is created in the relational database.

4. The method according to claim 1, wherein, in response to the creation instruction, creating the target data table in the relational database, based on the column information, comprising: Execute the creation instruction, and based on the column information corresponding to the basic data column in the creation instruction, create the target data table including the basic data column in the relational database; Based on the column information corresponding to the hidden data column, a hidden data column is added to the target data table to obtain the target data table including the basic data column and the hidden data column.

5. The method according to claim 1, wherein the data in the hidden data column corresponding to the data rows in the target data table includes tuples; wherein, The tuple includes the identifier of the device that generated the data in the data row corresponding to the basic data column, and a value; the values ​​in tuples that include the identifier of the same device are monotonically increasing.

6. The method according to claim 5, wherein the identifier of the device includes the device's IP address; or, the identifier of the device includes the device's IP address and port number.

7. The method according to claim 1, wherein the unique index of the tree structure includes a unique index based on a B-Tree or B+ Tree.

8. The method according to claim 1, wherein the target data table comprises a data table in which insert and / or delete operations are performed multiple times in a short period of time.

9. The method according to claim 8, wherein the target data table includes a temporary data table.

10. The method according to any one of claims 1-9, wherein the relational database includes OceanBase.

11. A query method for a relational database, the method comprising: Obtain a query instruction for performing a full table scan on a target data table; wherein, the target data table is a data table created in the relational database in response to a creation instruction for creating a target data table in the relational database, based on column information corresponding to the basic data columns in the target data table included in the creation instruction; the hidden data columns are extended data columns independent of the basic data columns; after the target data table is created, a unique tree-structured index is established for the target data table based on the data in the hidden data columns; In response to the query instruction, query conditions corresponding to the hidden data column are added to the query instruction to update the query instruction; The updated query instruction is executed to query the target data table based on the unique index of the tree structure, so as to retrieve the data of each data row record in the target data table.

12. A management device for a relational database, the device comprising: The first acquisition module acquires a creation instruction for creating a target data table in a relational database; wherein the creation instruction includes column information corresponding to the basic data columns in the target data table; The table creation module, in response to the creation instruction, creates the target data table in the relational database based on the column information, including the basic data columns and hidden data columns; wherein, the hidden data columns are extended data columns independent of the basic data columns; The index building module, after the target data table is created, builds a unique tree-structured index for the target data table based on the data in the hidden data column. The data in the hidden data column corresponding to the data rows in the target data table includes monotonically increasing values. In a query instruction that performs a full table scan of the target data table, the query instruction is rewritten by adding query conditions corresponding to the hidden data column, thereby transforming the query instruction into an instruction to query the target data table based on the unique index.

13. A query apparatus for a relational database, the apparatus comprising: The second acquisition module acquires a query instruction for performing a full table scan of the target data table; wherein, the target data table is a data table created in the relational database in response to a creation instruction for creating a target data table in the relational database, based on column information corresponding to the basic data columns in the target data table included in the creation instruction; the hidden data columns are extended data columns independent of the basic data columns; after the target data table is created, a unique tree-structured index is established for the target data table based on the data in the hidden data columns; The instruction update module, in response to the query instruction, adds query conditions corresponding to the hidden data column to the query instruction to update the query instruction; The table query module executes the updated query instruction and queries the target data table based on the unique index of the tree structure to retrieve the data of each data row record in the target data table.

14. An electronic device comprising: processor; Memory used to store processor-executable instructions; The processor implements the method as described in any one of claims 1-11 by executing the executable instructions.

15. A computer-readable storage medium having stored thereon computer instructions that, when executed by a processor, implement the method as described in any one of claims 1-11.

Images