Method and device for creating index

Abstract

According to the method and device for creating the index, in the method, the data table is scanned with the data page as the unit, the index is created for the data table, and the index creation efficiency can be improved. Moreover, in the index creation process, index creation pause and index creation recovery are supported, so that the robustness of the index creation process can be ensured.

Description

Technical Field

[0001] The present application relates to the technical field of database index creation, and in particular to a method and device for creating an index. Background Art

[0002] In a database system, indexes correspond to data in the database one by one, which ensures that the correct corresponding data can be found when searching for data using the index. In the current scheme for creating indexes, online creation is usually achieved based on two full table scans. The first full table scan creates the basic index of the table. Since users may update the data in the data table during the process of creating the basic index of the table, the second full table scan can complete the index of the data updated when the basic index was created for the first time. If the amount of data in the data table is relatively large, creating an index in this way will take a long time. Summary of the invention

[0003] The present application provides a method and device for creating an index, so as to shorten the time of online index creation and improve user experience.

[0004] In a first aspect, the present application provides a method for creating an index. Specifically, in the process of creating an index for a target data table, the method includes: first, obtaining a first snapshot of the target data table; then, obtaining at least one data page included in the target data table according to the first snapshot; finally, locking the first data page, and not locking other data pages except the first data page, wherein the first data page is any one of the at least one data page.

[0005] It should be understood that the locking operation means that data processing operations (addition, deletion, update, query) cannot be performed. In the embodiment of the present application, the locking operation on the first data page means that data processing operations cannot be performed on the first data page during the index creation process of the target data table. Correspondingly, the non-locking operation on other data pages except the first data page means that data processing operations can be performed on other data pages except the first data page.

[0006] Through the above technical solution, the index creation of the target data table is performed based on the data page, so that the index creation of the data table can be completed quickly without multiple full table scans, shortening the time for online index creation, and being able to increase the index generation rate and improve the performance of the system in providing external services, thereby improving the user experience.

[0007] In a possible implementation, performing a locking operation on the first data page includes:

[0008] The first data tuple of the first data page is obtained, and it is determined that the index of the first data tuple has not been created; and indexes are created for all data tuples in the first data page.

[0009] Through the above technical solution, it is possible to determine whether the index creation of each data page is completed based on the first data tuple of the data page, which can improve the efficiency of index creation.

[0010] In a possible implementation, before obtaining the first snapshot of the target data table, the method further includes:

[0011] Create relevant metadata of the target data table, wherein the relevant metadata includes the table name, index name, and index status of the target data table.

[0012] In a possible implementation, the method further includes:

[0013] When it is detected that there is a data insertion operation on other data pages except the first data page, the target data page where the target data tuple is to be inserted is determined, and the target data tuple is inserted into the target data page, wherein the target data tuple is a new data tuple to be inserted into the data table; an index of the target data tuple is created, and the index of the target data tuple is inserted into the index table.

[0014] Through the above technical solution, it is possible to perform a data insertion operation while creating an index for a target data table, thereby avoiding the problem of conflict between the data insertion operation and the index creation.

[0015] In a possible implementation, the method further includes:

[0016] When it is detected that there is a data update operation on a data page other than the first data page, it is determined that the data update operation needs to update the first index of the first data tuple; the second data page where the first data tuple is located is obtained, and when it is determined that the index creation of the second data page has been completed, the first index of the first data tuple is deleted, and the second index of the second data tuple is created on the third data page, the second data tuple is the data tuple after the first data tuple is updated, and the third data page is the target data page where the second data tuple is located; when it is determined that the second data page has not been indexed, an index of the second data page is created, the first index of the first data tuple is deleted, and the second index of the second data tuple is created on the third data page.

[0017] Through the above technical solution, it is possible to perform a data update operation while creating an index for a target data table, thereby avoiding the problem of conflict between the data update operation and the index creation.

[0018] In a possible implementation, the method further includes:

[0019] When it is detected that there is a data deletion operation on data pages other than the first data page, the third data tuple is obtained, and the fourth data page where the third data tuple is located is determined, and the third data tuple is the data tuple to be deleted; when it is determined that the fourth data page has not created an index, an index of the fourth data page is created, and the third data tuple and the corresponding index are deleted; when it is determined that the index creation of the fourth data page has been completed, the third data tuple and the corresponding index are deleted.

[0020] Through the above technical solution, it is possible to perform a data deletion operation while creating an index for a target data table, thereby avoiding the problem of a conflict between the data deletion operation and the index creation.

[0021] In a possible implementation, the method further includes:

[0022] A query instruction for a target data tuple is received; after determining that the index of the target data table is created, the target data tuple is searched according to the index.

[0023] Through the above technical solution, it is possible to execute a data query operation while creating an index for a target data table, thereby avoiding the problem of conflict between the data query operation and the index creation.

[0024] In a possible implementation, the method further includes:

[0025] When the index creation is detected to be interrupted, the index creation is resumed; each data page of the target data table is queried in turn, and if the index creation of the fifth data page is completed, the index creation of the fifth data page is skipped; if the index creation of the fifth data page is not completed, the index creation of the fifth data page is performed.

[0026] Through the above technical solution, when the service exits abnormally during the index creation process, after the service is restarted, it can be determined whether the current data page has completed index creation based on the first data tuple of the data page. If the data page has completed index creation, the data page can be skipped and the index creation of the data page that has not been indexed can be continued. In this way, there is no need to restart the index creation from the first data page, which can improve the efficiency of index creation.

[0027] In a possible implementation, the method further includes: receiving an operation instruction input by a user, where the operation instruction is used to pause the index creation.

[0028] Through the above technical solution, it is possible to control the working state of index creation through the operation instructions input by the user, so as to control the occupation of server resources by index creation, thereby reducing the impact of the online index creation process on online business. In addition, the index creation process supports create-pause-continue, which can solve the robustness problem of online index creation in large data capacity scenarios.

[0029] In a second aspect, the present application provides an apparatus for creating an index, the apparatus for creating an index comprising a processor; a memory and one or more computer programs; wherein the one or more computer programs are stored in the one or more memories, and the one or more computer programs include instructions, and when the instructions are called and executed by the one or more processors, the apparatus for creating an index executes the method of the above-mentioned first aspect and any possible design of the first aspect.

[0030] In a third aspect, the present application further provides a device for creating an index, which may include a processing module, and the processing module is used to execute the method of the first aspect and any possible design of the first aspect. Exemplarily, the processing module can be used to execute the processing operations in the first aspect and any possible design of the first aspect, such as locking the first data page and not locking other data pages except the first data page; or it can also execute index creation operations, etc.

[0031] In a possible implementation, the apparatus for creating an index may further include a transceiver module, and the processing module is used to execute the method of the first aspect and any possible design of the first aspect. Exemplarily, the processing module may be used to execute the acquisition operation or the receiving operation in the first aspect and any possible design of the first aspect, such as acquiring a first snapshot of the target data table; acquiring at least one data page included in the target data table; or receiving an operation instruction input by a user.

[0032] In a fourth aspect, an embodiment of the present application provides a database, comprising a processor; a memory and one or more computer programs; wherein the one or more computer programs are stored in the one or more memories, and the one or more computer programs include instructions, which, when called and executed by the one or more processors, enable the database to execute the above-mentioned first aspect and any possible design method of the first aspect.

[0033] In a fifth aspect, an embodiment of the present application provides a computer program product. When the computer program product is run on a device for creating an index, the device for creating an index executes the method of the first aspect of the embodiment of the present application and any possible design of the first aspect thereof.

[0034] In a sixth aspect, the present application also provides a computer-readable storage medium, wherein instructions are stored in the computer-readable storage medium. When the instructions are executed on a device for creating an index, the device for creating an index executes the method of the first aspect and any possible design of the first aspect.

[0035] For each of the above-mentioned aspects from the second to the sixth aspects and the technical effects that may be achieved by each of the aspects, please refer to the above-mentioned description of the technical effects that can be achieved by the various possible schemes in the first aspect, and no further details will be given here. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1A A schematic diagram of an application scenario provided for an embodiment of the present application;

[0037] Figure 1B A schematic diagram of an application scenario provided for an embodiment of the present application;

[0038] Figure 2 A schematic diagram of a system architecture provided for an embodiment of the present application;

[0039] Figure 3 A flowchart of a method for creating an index provided in an embodiment of the present application;

[0040] Figure 4 A flowchart of a method for creating an index provided in an embodiment of the present application;

[0041] Figure 5 A schematic diagram of index creation provided in an embodiment of the present application;

[0042] Figure 6 A flowchart of a method for creating an index provided in an embodiment of the present application;

[0043] Figure 7 A flowchart of a method for creating an index provided in an embodiment of the present application;

[0044] Figure 8 A flowchart of a method for creating an index provided in an embodiment of the present application;

[0045] Fig. 9 A flow chart of a data query method provided in an embodiment of the present application;

[0046] Fig.10 A structural block diagram of a device for creating an index provided in an embodiment of the present application;

[0047] Fig.11 A schematic diagram of the structure of a device for creating an index provided in an embodiment of the present application. DETAILED DESCRIPTION

[0048] The technical solutions in the embodiments of the present application will be described in detail below in conjunction with the drawings in the following embodiments of the present application.

[0049] In addition, at least one of the following embodiments includes one or more, wherein more means greater than or equal to two. In addition, it should be understood that in the description of this application, the words "first", "second" and the like are only used for the purpose of distinguishing the description.

[0050] Below, some terms in the embodiments of the present application are first explained to facilitate understanding by those skilled in the art.

[0051] 1. Create an index online

[0052] When creating an index for a table in a database system, it does not affect other operations of the user on the data in this table, such as adding, deleting, modifying and querying. In other words, when creating an index for the data table, other operations on the data table can be performed at the same time, and other operations are independent of index creation and do not affect each other.

[0053] 2. Snapshot

[0054] Refers to a read-only copy of a database or table, which is a mapping of all the data in the database table. The content of the snapshot is determined by the time point when the snapshot is created. It can also be understood that the snapshot can limit the range of data read. The range of data read by snapshots created at different time points may be different.

[0055] 3. Autonomous affairs

[0056] Refers to an independent transaction called from within a transaction. An autonomous transaction allows you to leave the environment of the calling transaction, perform some structured query language (SQL) operations, commit or undo these operations, and then return to the environment of the calling transaction and continue executing the transaction.

[0057] For example, Figure 1A FIG. 1 is a schematic diagram of an application scenario provided by an embodiment of the present application. Figure 1A As shown, the application scenario may include a server 10, and the server 10 may include a database system. Multiple data tables may be stored in the database system, and the data in the data tables have corresponding indexes. If data needs to be found, the corresponding data can be found through the index.

[0058] Another example, such as Figure 1B FIG. 1 is a schematic diagram of another application scenario provided by an embodiment of the present application. Figure 1BAs shown, the application scenario may include a terminal device 20, and the terminal device 20 may include a database system. Multiple data tables may be stored in the database system, and the data in the data tables have corresponding indexes. If data needs to be found, the corresponding data can be found through the index.

[0059] That is to say, the online index creation method of the embodiment of the present application can be applied to the database system on the server side or the terminal side. It should be understood that the product form corresponding to the online index creation method of the embodiment of the present application can be a program code included in the database kernel software and deployed on the server hardware.

[0060] like Figure 2 FIG. 1 is a schematic diagram of a system architecture provided in an embodiment of the present application. Figure 2 As shown, the system architecture may include: a connection management module 201 , an SQL engine module 202 , a storage management module 203 , a system table 204 , and a transaction management module 205 .

[0061] Among them, the connection management module 201 is used to manage different connections for external access; the SQL engine module 202 includes an SQL parser, a query optimizer, and a query executor, which are used to complete the analysis and conversion of SQL requests in the system, and finally convert them into operations on data in the physical storage medium. Specifically, the SQL parser is a component used to convert SQL statements into executable instructions. Its main function is to decompose the input SQL statements into grammatical units, and then convert these grammatical units into internally represented data structures, and finally generate an executable query plan; the query optimizer is a component used to optimize the user's SQL request internally, generate (or reuse) an execution plan and transmit it to the storage engine to operate the data; the query executor is used to undertake the optimal execution plan generated by the optimizer, and execute it in a pipeline manner according to the execution plan, operate on the data in the underlying storage engine, and return the results to the client.

[0062] The storage management module 203 includes an index management module, a memory management module, and an external storage management module, which are responsible for storing and managing actual physical data (including index data and business data). Specifically, the index management module is responsible for managing the index data of business data in the system, including the creation and cleaning of index data; the memory management module is responsible for managing the data on the system main memory in the system; and the external storage management module is responsible for managing the data on the system external storage medium in the system.

[0063] The transaction management module 205 includes a log management module, a concurrency control module, a lock management module, etc. Specifically, the log management module and the transaction management module are used to complete the consistency support for the operation transaction, and the lock management module and the concurrency control module provide consistency support for concurrent access to data by transactions. The system table 204 is used to store metadata of user data.

[0064] like Figure 3 As shown, it is a flow chart of a method for creating an index provided by an embodiment of the present application, see Figure 3 As shown, the method may include the following steps:

[0065] Step 301: Create index-related metadata of the target data table.

[0066] For the convenience of description, in the embodiment of the present application, the data table to be indexed may be referred to as a target data table.

[0067] The index-related metadata may include the table name, index name, and index status of the target data table.

[0068] In a possible implementation, when creating index-related metadata, the index status may be marked as READY in a system table.

[0069] Step 302: Obtain a first snapshot of the target data table.

[0070] For the convenience of description, the snapshot of the target data table at the current time can be recorded as: the first snapshot. It should be understood that the snapshots obtained at different time points may be different, and the first snapshot can limit the range of data that can be read. For example, if the first snapshot is obtained at 10:00 on October 23, 2023, then the range of data that can be read is the data of the target data table before 10:00 on October 23, 2023.

[0071] In some embodiments, a first snapshot of the target data table may be acquired and saved to a system table.

[0072] In some embodiments, the shared lock of the target data table can be obtained to wait for all transactions that modify the table to end, ensuring that when creating an index on the target data table, there will be no data inconsistency caused by unfinished transactions that modify the table. In addition, after obtaining the shared lock of the target data table, the current index creation state can be changed to READY state, and the current snapshot can be saved in the system table as an index creation snapshot, and then the table shared lock can be released, so that all data manipulation language (DML) transactions after starting to create the index can sense that the index is being created and need to process the data index.

[0073] Step 303: Acquire at least one data page included in the target data table according to the first snapshot.

[0074] Each data table in the database system may include at least one data page.

[0075] In some embodiments, the target data table in the database system may be scanned based on the first snapshot, and then all data pages included in the target data table under the first snapshot may be acquired, and an index may be created in units of data pages.

[0076] Step 304: performing a locking operation on the first data page, and not performing a locking operation on other data pages except the first data page.

[0077] For the convenience of description, any data page in the target data table may be recorded as a “first data page.” The first data page is one of at least one data page included in the target data table obtained based on the first snapshot.

[0078] It should be understood that in the embodiment of the present application, the first data page of the target data table refers to any one of the at least one data page included in the target data table obtained based on the first snapshot. Specifically, the locking operation refers to locking the data page, and the data addition, deletion, query, modification (update) operations cannot be performed. In some embodiments, during the process of indexing the target data table, the first data page can be locked, and the locking operation is not performed on the other data pages except the first data page in the at least one data page, so that data processing operations can be performed on other data pages while the index is created for the first data page.

[0079] In some embodiments, at least one data page included in the target data table may have some / all data pages for which no index is created. Therefore, it is only necessary to create indexes for the data pages for which no index is created.

[0080] It should be understood that in the embodiment of the present application, the transaction for index creation can be called a main transaction, which can scan all data pages of the target data table, and then create an index for each data page by creating an autonomous transaction (or, also called a sub-transaction). Among them, one autonomous transaction processes the index of one data page.

[0081] In a possible implementation, after indexes for all data pages in the target data table are created, the index state may be marked as a VALID (valid) state, and updated from a READY state to a VALID state, to indicate that index creation is complete.

[0082] The above step 304 is described in detail below in conjunction with the specific process. Figure 4FIG. 1 is a flowchart of a method for creating an index for a data page provided in an embodiment of the present application. Figure 4 As shown, the method may include the following steps:

[0083] It should be understood that Figure 4 The embodiment shown is described by taking a data page as an example. The implementation of other data pages can be found in Figure 4 Description of the illustrated embodiment.

[0084] Step 401: Obtain the first data tuple of the first data page.

[0085] Step 402: Determine whether the index of the first data tuple of the first data page has been created. If the index of the first data tuple has been created, then end the step; if the index of the first data tuple has not been created, then continue to step 403.

[0086] In the embodiment of the present application, the index of the target data table is created online in units of data pages, so when judging whether a data page has an index, it is only necessary to judge whether the first data tuple of the data page has an index. If the first data tuple of the data page has an index, it means that the index has been created; if the first data tuple of the data page does not have an index, it means that the index has not been created for the data page.

[0087] In some embodiments, it is possible to query from the index being built whether the index corresponding to the first data tuple of the first data page exists. If the index corresponding to the first data tuple exists, it means that the index of the first data page has been created, and the index creation task of the first data page is skipped. If the index corresponding to the first data tuple does not exist, it is necessary to create the index creation task of the first data page.

[0088] In other embodiments, it is also possible to query from the index table that has been created whether the index corresponding to the first data tuple of the first data page exists. If the index of the first data tuple exists, it means that the index of the first data page has been created, and the index creation task of the first data page is skipped; if the index of the first data tuple does not exist, it is necessary to create the index creation task of the first data page.

[0089] Step 403: Create an index for all data tuples of the first data page.

[0090] In some embodiments, an index corresponding to all data tuples included in the first data page may be constructed based on the scanned data page and index-related metadata, and the index may be inserted into a corresponding index table.

[0091] Through the above embodiments, the index creation of the data table can be completed quickly without multiple full table scans, which can improve the index generation rate and the performance of the system in providing external services, thereby improving the user experience.

[0092] Furthermore, in the embodiment of the present application, if the service exits abnormally during the index creation process, since the metadata of the index exists and the indexed data page is created based on the first snapshot, when the service is restarted, it is only necessary to combine the unfinished index creation tasks in the system table to continue to complete the index creation task. In addition, the index creation in the embodiment of the present application is based on the data page, so when the service is restarted due to an abnormality, it can be determined whether the current data page has completed the index creation based on the first data tuple of the data page. If the data page has completed the index creation, the data page can be skipped and the index creation of the data page that has not been indexed can continue.

[0093] In some embodiments, an interface that supports the suspension and resumption of index creation may also be provided in the embodiments of the present application. The interface can adjust the online index creation process to a suspended state, and the interface can also be called to resume the index creation process from a suspended state to continue creating the index. That is to say, in the embodiments of the present application, the working state of index creation can be controlled through the interface to control the occupation of server resources by index creation, thereby reducing the impact of the online index creation process on online business. In addition, the index creation process supports creation-pause-resume, which can solve the robustness problem of online index creation in large data capacity scenarios.

[0094] For example, taking SQL syntax as an example, the code for suspending index creation is as follows: CREATE INDEX index_name ON table_name (column_name) SUSPEND; the code for resuming index creation is as follows: CREATE INDEX index_name ON table_name (column_name) RESUME. The user can enter the corresponding code on the operation interface of the database system to control the process of index creation.

[0095] Since the index creation in the embodiment of the present application is online index creation, other user operations can be performed synchronously while the index is being created. Figure 5 FIG. 1 is a schematic diagram of an online index creation provided by an embodiment of the present application. Figure 5 As shown, assuming that the target data table includes data page 1 (page1), data 2 (page2), ..., data page N (pageN), and index creation is currently being performed on data page X, the user can perform DML operations on data page 2 or on data page N-1.

[0096] For example, assume that transaction 1 is an index creation transaction, which is currently creating an index on data page X, and transaction 2 is a DML operation transaction, which can be a DML operation for the user on data page 2. Since index creation is performed in units of data pages in the order of data pages, when the user performs a DML operation on data page 2, the index creation task for data page 2 has been completed. At this time, transaction 2 does not need to create a separate index for data page 2, and can directly process the corresponding index for the DML operation on data page 2.

[0097] In another example, suppose transaction 1 is an index creation transaction, which is currently creating an index on data page X, and transaction 3 is a DML operation transaction, which can be a DML operation for the user on data page (N-1). Since index creation is performed in units of data pages in the order of data pages, when the user performs a DML operation on data page (N-1), the index has not yet been created on data page (N-1). At this time, transaction 2 can first create an index on data page (N-1), and then perform a DML operation on data page 2 after the index creation is completed. When the index creation task of transaction 1 is executed on data page (N-1), the index creation of data page (N-1) can be skipped, and the index creation can continue on the next data page.

[0098] The index creation method of the embodiment of the present application is introduced below in combination with different scenarios.

[0099] Scenario 1: Insert data (add data) scenario

[0100] like Figure 6 As shown, it is a flow chart of a method for creating an index provided by an embodiment of the present application, see Figure 6 As shown, the method may include the following steps:

[0101] Step 601: Obtain a target data page where a target data tuple is to be inserted, and insert the target data tuple into the target data page.

[0102] It should be understood that the target data tuple refers to a new data tuple to be inserted into the data table, and the target data page refers to a data page into which the target data tuple is to be inserted.

[0103] Step 602: Determine whether the target data table has an index. If the index exists, continue to step 603; if the index does not exist, end the step.

[0104] Step 603: Create an index for the target data tuple and insert the index into the index table.

[0105] In the embodiment of the present application, data inserted concurrently during index creation may be invisible to the index creation thread. Therefore, the concurrent data insertion transaction does not conflict with the index creation transaction. Through the above embodiment, new data can be inserted into the data table while the index is being created for the data table.

[0106] It should be understood that Figure 6 The illustrated embodiment is an operation performed by a concurrent data insert transaction. For an index creation transaction, the index creation transaction only needs to determine whether an index has been created for a data page.

[0107] Below Figure 5 The scenario shown is an example of Figure 6 The process in the illustrated embodiment is described.

[0108] Assume that the target data page to be inserted into the target data tuple is data page 2. At this time, the transaction of the DML operation can determine whether there is an index on the target data table. If there is an index, the index tuple of the target data can be directly built on data page 2 and inserted into the index table. In other words, for the data insertion scenario, it is not necessary to check whether the data page has an index, but only whether the target data table has an index.

[0109] Scenario 2: Update (modify) data scenario

[0110] like Figure 7 As shown, it is a flow chart of a method for creating an index provided by an embodiment of the present application, see Figure 7 As shown, the method may include the following steps:

[0111] Step 701: Determine whether the data update operation needs to update the index of the old data tuple.

[0112] In some embodiments, when there is a concurrent operation of updating a data tuple while creating an index, if the data update operation needs to update the index of the old data tuple, continue to execute step 702; if the index of the old data tuple does not need to be updated, end the step.

[0113] As a possible implementation method, it can be determined whether the data update operation is a heaponly tuple (HOT) update operation. If it is not a Hot update operation, continue to execute step 702; if it is a Hot update operation, end the step. Specifically, it can be determined whether the position of the data tuple to be updated needs to be updated in other data pages, that is, whether the old data tuple and the new data tuple are in the same data page. If the new data tuple and the old data tuple are in the same data page, there is no need to update the index of the old data tuple, and the step ends; if the new data tuple and the old data tuple are not in the same data page, the index of the old data tuple needs to be updated.

[0114] It should be understood that the HOT update operation refers to the update by modifying the pointer to locate the new tuple without inserting the corresponding index tuple. When the Hot update operation is satisfied, only the data tuple needs to be updated without processing the related index.

[0115] Step 702: Determine whether an index is currently being created. If an index is being created, continue to step 703. If an index is not being created, delete the index corresponding to the old data tuple.

[0116] In some embodiments, it can be determined whether an index is currently being created. If an index is not being created, it means that the index of the old data tuple has been created, and the corresponding index can be deleted, and then a new index can be created. If the index of the old data tuple has not been created, it is necessary to complete the index first (or: create the index). It should be understood that when the index of the old data tuple has not been created, an index can be created for the corresponding data page through an autonomous transaction.

[0117] Step 703: Obtain the second data page where the old data tuple is located, and obtain the first data tuple of the second data page.

[0118] For the convenience of description, in the embodiment of the present application, the data page where the old data tuple is located can be recorded as: the second data page, and the target data page where the new data tuple is located can be recorded as: the third data page.

[0119] In some embodiments, the second data page is one of the data pages included in the target data table acquired based on the first snapshot.

[0120] Step 704: Determine whether the index of the first data tuple of the second data page exists. If not, continue to execute step 705; if the index exists, delete the old index of the old data tuple, create a new index for the new data tuple on the third data page, and insert the new index into the index table.

[0121] In some embodiments, when it is necessary to update a data tuple in a data table, it is necessary to determine whether the data page (second data page) where the old data tuple is located has been indexed. Specifically, the first data tuple of the second data page can be obtained to determine whether the index of the first data tuple exists. If the index of the first data tuple exists, it means that the index of the second data page has been created. At this time, the old index of the old data tuple can be deleted, and a new index of the new data tuple can be created on the new data page (third data page), and the new index is inserted into the index table; if the index of the first data tuple does not exist, it means that the index of the second data page has not been created.

[0122] Step 705: Create an index for the second data page, delete the old index of the old data tuple, create a new index for the new data tuple on the third data page, and insert the new index into the index table.

[0123] In some embodiments, since the second data page is obtained based on the first snapshot, when the second data page where the old data tuple is located has not yet created an index, the concurrently operated transaction can first create an index for the second data page, and after the creation is completed, delete the old index of the old data tuple. In addition, a new index for the new data tuple can be created on the new data page, and the new index for the new data tuple can be inserted into the index table.

[0124] Below Figure 5 The scenario shown is an example of Figure 7 The process in the illustrated embodiment is described.

[0125] Example 1: Assuming that the target data page of the new data tuple is data page 2, and the data page of the old data tuple is data page 1, that is, the concurrent operation needs to update the data page, then it can be determined whether the index creation of the old data page, that is, data page 1, is completed. Figure 5 In the schematic diagram shown, the index creation has been completed on data page 1. At this time, the old index of the old data tuple can be deleted, and then a new index can be created on data page 2.

[0126] Example 2: Assuming that the target data page of the new data tuple is data page 1, and the data page of the old data tuple is data page (N-1), that is, the concurrent operation needs to update the data page, then it can be determined whether the index creation of data page (N-1) is completed. Figure 5 In the schematic diagram shown, the index creation task has been carried out to data page X, and the index creation task has not yet started on data page (N-1). Therefore, the index creation task can be completed on the old data page, that is, data page (N-1), and then the old index of the old data tuple is deleted, and a new index is created on the new data page, that is, data page 1.

[0127] It should be understood that step 703, step 704 and Figure 4 The implementation of step 402 and step 403 in the embodiment shown is similar, and the details can be found in Figure 4 The description in the illustrated embodiment will not be repeated here.

[0128] Scenario 3: Deleting data

[0129] like Figure 8 As shown, it is a flow chart of a method for creating an index provided by an embodiment of the present application, see Figure 8 As shown, the method may include the following steps:

[0130] Step 801: Acquire a data tuple to be deleted, and determine the fourth data page where the data tuple to be deleted is located.

[0131] For the convenience of description, in the embodiment of the present application, the data page where the data tuple to be deleted is located can be recorded as: the fourth data page.

[0132] In some embodiments, the fourth data page is one of the data pages included in the target data table acquired based on the first snapshot.

[0133] Step 802: Obtain the first data tuple of the fourth data page.

[0134] Step 803: Determine whether the index of the first data tuple of the fourth data page exists. If so, delete the data tuple to be deleted and the corresponding index. If not, continue to execute step 804.

[0135] In some embodiments, when a data tuple needs to be deleted from a data table, it is necessary to determine whether an index has been created for the data page (fourth data page) where the data tuple to be deleted is located. Specifically, the first data tuple of the fourth data page can be obtained to determine whether the index of the first data tuple exists. If the index of the first data tuple exists, it means that the index of the fourth data page has been created, and the index of the data tuple to be deleted can be directly deleted at this time; if the index of the first data tuple does not exist, it means that the index of the fourth data page has not been created.

[0136] Step 804: Create an index for the fourth data page, and delete the data tuple to be deleted and the corresponding index.

[0137] In some embodiments, when the index of the fourth data page has not been created, an autonomous transaction may be used to create (complete) an index for the fourth data page, and then after the index is completed, a data tuple deletion operation and an index deletion operation are performed.

[0138] Below Figure 5 The scenario shown is an example of Figure 8 The process in the illustrated embodiment is described.

[0139] Example 1: Assuming that the data page where the data tuple to be deleted is located is data page 2, it can be determined whether the index creation of data page 2 is completed. Figure 5 In the schematic diagram shown, the index creation of data page 2 has been completed, and the index of the data tuple to be deleted can be directly deleted at this time.

[0140] Example 2: Assuming that the data page where the data tuple to be deleted is located is data page (N-1), it can be determined whether the index creation of data page (N-1) is completed. Figure 5In the diagram shown, the index creation task has been carried out to data page X, and the index creation task has not yet started on data page (N-1). Therefore, if the DML operation needs to delete data on data page (N-1), it is necessary to complete the index creation task on data page (N-1) first, and then delete the data tuple to be deleted and the corresponding index.

[0141] It should be understood that the index creation transaction and the concurrent operation transaction (DML operation transaction) in the embodiment of the present application are two independent transactions (which can also be understood as: threads or services). Figure 3 and Figure 4 The illustrated embodiment is performed by an index creation transaction. Figure 6 , Figure 7 and Figure 8 It is executed by concurrent operations.

[0142] Scenario 4: Data query operation

[0143] For example, Fig. 9 As shown, it is a flow chart of a data query method provided in an embodiment of the present application, see Fig. 9 As shown, the method may include the following steps:

[0144] Step 901: Receive a query instruction for a target data tuple.

[0145] In some embodiments, the database system may receive a query instruction for target data input by a user on an operation interface, and then search for corresponding data tuples based on the query instruction.

[0146] Step 902: Determine whether the target data table has completed index creation. If the target data table has completed index creation, continue to step 903; if the target data table has not completed index creation, continue to step 904.

[0147] Step 903: Obtain the target data tuple according to the index.

[0148] Step 904: query the data tuples in the target data table in sequence to obtain the target data tuples.

[0149] In some embodiments, during the index creation process, the index status is READY, and when the index creation of the target data table is completed, the index status can be updated to VALID. In the READY state, the index tuples of some data have not been created yet, and the target data tuples can be obtained by full table scanning; in the VALID state, the indexes of all data tuples of the target data table have been created, and the indexes can be used to query the data tuples, thereby speeding up the data query efficiency.

[0150] It should be noted that all or part of the above embodiments provided in this application can be freely and arbitrarily combined with each other, and the combined technical solutions are also within the protection scope of this application.

[0151] In the embodiments provided by the present application, the method provided by the embodiments of the present application is introduced from the perspective of the electronic device as the execution subject. In order to implement the functions in the methods provided by the embodiments of the present application, the electronic device may include a hardware structure and / or a software module, and implement the above functions in the form of a hardware structure, a software module, or a hardware structure plus a software module. Whether a function of the above functions is executed in the form of a hardware structure, a software module, or a hardware structure plus a software module depends on the specific application and design constraints of the technical solution.

[0152] For example, Fig.10 As shown, some other embodiments of the present application disclose a device for creating an index. Fig.10 As shown, the apparatus 1000 for creating an index includes: a transceiver module 1001 and a processing module 1002. The processing module 1002 is used to perform the index creation operation, data processing operation, etc. in the above-mentioned embodiment. The transceiver module 1001 is used to perform the acquisition operation, receiving operation, etc. in the above-mentioned embodiment. Exemplarily, the transceiver module 1001 is used to obtain a first snapshot of a target data table; obtain at least one data page included in the target data table according to the first snapshot; the processing module 1002 is used to lock the first data page, and not lock other data pages except the first data page.

[0153] like Fig.11 As shown, some other embodiments of the present application disclose a device for creating an index. Fig.11 As shown, the apparatus 1100 for creating an index includes: a processor 1101 ; a memory 1102 ; a transceiver 1103 ; and one or more computer programs 1104 (not shown in the figure), and the above-mentioned components can be connected via one or more communication buses 1105 .

[0154] The memory 1102 stores one or more computer programs. When the instructions are called and executed by the processor 1101, the apparatus 1100 for creating an index performs the method steps in the above embodiment. The transceiver 1103 can be used to receive operation instructions input by the user, such as starting an index creation task, pausing an index creation task, and resuming an index creation task. It should be understood that Fig.11 The transceiver 1103 in the illustrated embodiment is used to perform Fig.10 The steps performed by the transceiver module 1001 in the illustrated embodiment. The processor 1101 is configured to perform Fig.10Steps performed by the processing module 1002 in the illustrated embodiment.

[0155] In the embodiment of the present application, the processor 1101 can be a general-purpose processor, a digital signal processor, an application-specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, a discrete hardware component, and can implement or execute the methods, steps and logic block diagrams disclosed in the embodiment of the present application. The general-purpose processor can be a microprocessor or any conventional processor, etc. The steps of the method disclosed in the embodiment of the present application can be directly embodied as a hardware processor to be executed, or the hardware and software modules in the processor can be combined and executed. The software module can be located in the memory 1102, and the processor 1101 reads the program instructions in the memory 1102 and completes the steps of the above method in combination with its hardware.

[0156] In the embodiment of the present application, the memory 1102 may be a non-volatile memory, such as a hard disk drive (HDD) or a solid-state drive (SSD), etc., or a volatile memory (volatile memory), such as RAM. The memory may also be any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory in the embodiment of the present application may also be a circuit or any other device that can implement a storage function, for storing instructions and / or data.

[0157] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the above-described devices and units can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0158] Based on the above embodiments, the present application also provides a database, which may include a processor; a memory; and one or more computer programs; wherein the one or more computer programs are stored in the one or more memories, and the one or more computer programs include instructions, and when the instructions are called and executed by the one or more processors, the database executes the method provided in the above embodiments.

[0159] Based on the above embodiments, the present application further provides a computer-readable storage medium, in which a computer program is stored. When the computer program is executed by a computer, the computer executes the method provided in the above embodiments.

[0160] A computer program product is also provided in an embodiment of the present application, including instructions, which, when executed on a computer, enable the computer to execute the method provided in the above embodiment.

[0161] The present application embodiment is described with reference to the flowchart and / or block diagram of the method, device (system) and computer program product according to the present application embodiment. It should be understood that each flow process and / or box in the flow chart and / or block diagram and the combination of the flow chart and / or box in the flow chart and / or block diagram can be realized by instructions. These instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processing machine or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device produce a device for realizing the function specified in one flow chart or multiple flows and / or one box or multiple boxes of the block diagram.

[0162] These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce a manufactured product including an instruction device that implements the functions specified in one or more processes in the flowchart and / or one or more boxes in the block diagram.

[0163] These computer program instructions may also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, whereby the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one or more processes in the flowchart and / or one or more boxes in the block diagram.

Claims

1. A method for creating an index, characterized in that: In the process of creating an index for a target data table, the method includes: Get the first snapshot of the target data table; Acquire at least one data page included in the target data table according to the first snapshot; A locking operation is performed on a first data page, and no locking operation is performed on other data pages except the first data page, wherein the first data page is any one of the at least one data page.

2. The method according to claim 1, characterized in that The locking operation on the first data page includes: Obtaining a first data tuple of the first data page, and determining that an index of the first data tuple has not been created; Index creation is performed for all data tuples in the first data page.

3. The method according to claim 1 or 2, characterized in that The method further comprises: When it is detected that a data tuple insertion operation exists on other data pages except the first data page, a target data page for inserting a target data tuple is determined, and the target data tuple is inserted into the target data page, wherein the target data tuple is a new data tuple to be inserted into the data table; An index of the target data tuple is created, and the index of the target data tuple is inserted into an index table.

4. The method according to claim 1 or 2, characterized in that: The method further comprises: When detecting that a data update operation exists on other data pages except the first data page, determining that the data update operation needs to update a first index of a first data tuple; Obtain the second data page where the first data tuple is located, and when it is determined that the index creation of the second data page has been completed, delete the first index of the first data tuple, and create the second index of the second data tuple on the third data page, where the second data tuple is the data tuple after the first data tuple is updated, and the third data page is the target data page where the second data tuple is located.

5. The method according to claim 4, characterized in that The method further comprises: When it is determined that the second data page has no index created, an index for the second data page is created, the first index for the first data tuple is deleted, and a second index for the second data tuple is created on the third data page.

6. The method according to claim 1 or 2, characterized in that: The method further comprises: When it is detected that a data deletion operation exists on other data pages except the first data page, a third data tuple is acquired, and a fourth data page where the third data tuple is located is determined, and the third data tuple is a data tuple to be deleted; When it is determined that the fourth data page has no index created, an index for the fourth data page is created, and the third data tuple and the corresponding index are deleted.

7. The method according to claim 6, characterized in that The method further comprises: When it is determined that the index creation of the fourth data page has been completed, the third data tuple and the corresponding index are deleted.

8. The method according to claim 1 or 2, characterized in that: The method further comprises: Receiving a query instruction of a target data tuple; After determining that the index of the target data table is created, the target data tuple is queried according to the index.

9. The method according to any one of claims 1 to 8, characterized in that: The method further comprises: When the index creation is detected to be interrupted, resuming the index creation; querying each data page of the target data table in sequence, and if the index creation of the fifth data page is completed, skipping the index creation of the fifth data page; If the index creation of the fifth data page is not completed, the index creation is performed on the fifth data page.

10. The method according to any one of claims 1 to 9, characterized in that: The method further comprises: An operation instruction input by a user is received, where the operation instruction is used to pause the index creation.

11. A device for creating an index, characterized in that: The index creation device includes a processor; a memory; and one or more computer programs; Wherein, the one or more computer programs are stored in the one or more memories, and the one or more computer programs include instructions, which, when called and executed by the one or more processors, enable the index creation device to execute the method as described in any one of claims 1 to 10.

12. A device for creating an index, characterized in that: The apparatus for creating an index comprises a processing module, and the processing module is used to execute the method according to any one of claims 1-10.

13. The device according to claim 12, characterized in that The apparatus for creating an index further includes a transceiver module, and the transceiver module is used to execute the method according to any one of claims 1-10.

14. A database, characterized in that: The database comprises a processor; a memory; and one or more computer programs; The one or more computer programs are stored in the one or more memories, and the one or more computer programs include instructions, which, when called and executed by the one or more processors, enable the database to execute the method according to any one of claims 1 to 10.

15. A computer program product, characterized in that When the computer program product is run on a device for creating an index, the device for creating an index is enabled to perform the method according to any one of claims 1 to 10.

16. A computer-readable storage medium having instructions stored therein, characterized in that: When the instruction is executed on a device for creating an index, the device for creating an index executes the method according to any one of claims 1 to 10.

Images