A method, device, equipment and storage medium for creating a secondary index of a horizontal partition table

Abstract

The present invention discloses a method, device, equipment and storage medium for creating a secondary index of a horizontal partitioned table. The method comprises: after receiving a secondary index creation instruction, setting the index status of the secondary index of the partition root table in the horizontal partitioned table corresponding to the instruction to an invalid state; filling the secondary index of the corresponding leaf-level partition subtable in the horizontal partitioned table with index data through an assigned asynchronous subtask; after detecting that each leaf-level partition subtable has completed index data filling, setting the index status of the secondary index of the partition root table to a valid state to obtain the secondary index. In an embodiment of the present invention, index data is filled for the secondary index of the corresponding leaf-level partition subtable in the horizontal partitioned table through an assigned asynchronous subtask, which solves the problem of long time consumption when creating the secondary index, improves the efficiency of secondary index creation, and thus improves the user experience.

Description

Technical Field

[0001] Embodiments of the present invention relate to the field of databases, and in particular to a method, apparatus, device, and storage medium for creating a secondary index of a horizontally partitioned table. Background Art

[0002] When partitioned tables have very large amounts of data, creating secondary indexes can be time-consuming. Solutions from major database vendors primarily include asynchronous index creation using async and index creation using parallelism. HBase's async index creation can be divided into two steps: the first step is to create the index asynchronously using async. Upon completion, the index is invalid, meaning it contains no data. The second step is to populate the target file with index data, which is then updated to a valid state. If the partitioned table data changes during the asynchronous index creation process, inconsistencies between the index data and the actual data in the partitioned table may occur, necessitating correction of the index data. Databases such as ORACLE and PostgreSQL support index creation using parallelism. Parallel index creation requires specifying the maximum number of parallel processes allowed by the database. Parallel index creation utilizes the producer-consumer model, whereby multiple parallel processes are allocated to execute operations such as data scans and joins simultaneously when executing a structured query language (SQL). These multiple processes collaborate to complete the same SQL statement.

[0003] It can be seen that asynchronous index creation using async does not really improve the efficiency of index data filling. It only splits index creation into two parts. Index creation using parallel specifies the degree of parallelism and delegates a SQL statement to multiple processes to complete the work collaboratively, exchanging N times the resource consumption for time. However, the index B-tree must be filled with data in an orderly manner. If the coupling between producers and consumers is too high during index data filling, the parallel efficiency will be reduced. Summary of the Invention

[0004] The present invention provides a method, apparatus, device and storage medium for creating a secondary index of a horizontal partition table, so as to improve the efficiency of creating the secondary index.

[0005] In a first aspect, an embodiment of the present invention provides a method for creating a secondary index of a horizontally partitioned table, the method comprising:

[0006] After receiving the secondary index creation instruction, the index state of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction is set to an invalid state;

[0007] Filling index data for the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtask;

[0008] After detecting that each leaf-level partition sub-table has completed filling the index data, the index state of the secondary index of the partition root table is set to a valid state to obtain the secondary index.

[0009] In a second aspect, an embodiment of the present invention further provides a device for creating a secondary index of a horizontal partition table, the device comprising:

[0010] A state setting module is configured to, upon receiving a secondary index creation instruction, set the index state of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state;

[0011] A data filling module is used to fill index data for the secondary index of the corresponding leaf-level partition sub-table in the horizontal partition table through the assigned asynchronous sub-task;

[0012] The index obtaining module is used to set the index status of the secondary index of the partition root table to a valid state after detecting that the index data filling of each leaf-level partition sub-table is completed, and obtain the secondary index.

[0013] In a third aspect, an embodiment of the present invention further provides a computer device, characterized in that the computer device includes:

[0014] one or more processors;

[0015] a memory for storing one or more programs,

[0016] When the one or more programs are executed by the one or more processors, the one or more processors implement the method for creating a secondary index for a horizontal partitioned table as described in the first aspect above.

[0017] In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method for creating a secondary index for a horizontal partitioned table as described in the first aspect above.

[0018] In the technical solution provided by the embodiment of the present invention, first, after receiving the secondary index creation instruction, the index status of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction is set to an invalid state; through the assigned asynchronous subtask, the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table is filled with index data; after detecting that the index data filling of each leaf-level partition subtable is completed, the index status of the secondary index of the partition root table is set to a valid state to obtain the secondary index. In the embodiment of the present invention, the secondary index of the corresponding partition subtable in the horizontal partition table is filled with index data through the assigned asynchronous subtask, which solves the problem of long time consumption when creating the secondary index, improves the efficiency of secondary index creation, and thus improves the user experience. Compared with the existing technology, the secondary index creation method for the horizontal partition table adopted solves the problem of inconsistency between the index data and the actual data in the partition table when the partition table data changes during the asynchronous index creation process of async asynchronous index creation, and solves the problem of high resource consumption when creating the index with parallel specifying the parallelism, thereby improving efficiency to a certain extent. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 A flowchart of a method for creating a secondary index for a horizontally partitioned table provided in Example 1 of the present invention;

[0020] Figure 2 A flowchart of a method for creating a secondary index for a horizontally partitioned table provided in the second embodiment of the present invention;

[0021] Figure 3 A flowchart of a method for creating a secondary index for a horizontally partitioned table provided in the second embodiment of the present invention;

[0022] Figure 4 A schematic diagram of the structure of a device for creating a secondary index for a horizontal partition table provided in a third embodiment of the present invention;

[0023] Figure 5 A schematic diagram of the structure of a computer device provided in Example 4 of the present invention. DETAILED DESCRIPTION

[0024] The present invention will be further described in detail below with reference to the accompanying drawings and examples. It will be understood that the specific embodiments described herein are intended only to illustrate the present invention and are not intended to limit the present invention. It should also be noted that, for ease of description, the accompanying drawings only illustrate portions relevant to the present invention, not all structures.

[0025] Example 1

[0026] Figure 1This is a flowchart of a method for creating a secondary index for a horizontally partitioned table, provided in Example 1 of the present invention. This embodiment is applicable to situations where secondary index creation is inefficient. The method can be performed by a device for creating a secondary index for a horizontally partitioned table. The device can be implemented using hardware and / or software and is typically configured in a computer device. Specifically, the method includes the following steps:

[0027] S110: After receiving the secondary index creation instruction, set the index status of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state.

[0028] The secondary index creation instruction can be understood as an instruction generated when creating a secondary index of a horizontal partitioned table.

[0029] In this embodiment, a horizontally partitioned table can be understood as splitting a table with a large amount of data into several sub-tables with the same table structure. Based on the partition table's hierarchy, it can be divided into: partition root table, intermediate table, and leaf-level partition sub-table. Data is stored only at the lowest level of the partition table hierarchy—that is, only in leaf-level partition sub-tables.

[0030] It should be noted that the index status of a secondary index can include a valid state and an invalid state. The valid state can be understood as the state in which the secondary index exists and is available; the invalid state can be understood as the state in which the secondary index exists but is unavailable.

[0031] It should be noted that since the horizontal partitioned table must be blocked during secondary index creation, the secondary index of the partitioned root table in the horizontal partitioned table must be set to invalid. This prevents the horizontal partitioned table from being dropped by the user. It also prevents inserts, deletes, and updates during index creation, which could lead to data inconsistencies between the clustered index and the secondary index. However, queries on the horizontal partitioned table are allowed. A clustered index is defined as a database table where the physical order of the data is the same as the logical (index) order of the key values. A table can only have one clustered index.

[0032] In this embodiment, after receiving a secondary index creation instruction for a horizontal partition table, it is necessary to set the index state of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state in order to perform the next operation.

[0033] S120 . Fill index data into the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtask.

[0034] Asynchronous subtasks can be understood as asynchronous subprocesses or threads. When creating an index, there is a main task and asynchronous subtasks. The main task delegates some of its execution to the asynchronous subtasks. The main task can be understood as the main process or thread.

[0035] In this embodiment, index data population can be understood as the process of populating the secondary indexes of corresponding leaf-level partitioned subtables in a horizontally partitioned table with data through assigned asynchronous subtasks. It is understood that, due to the characteristics of horizontally partitioned tables, data is only stored in leaf-level partitioned subtables, and the secondary index b-trees of each leaf-level partitioned subtable are independent. In this case, the data being populated can be understood as the data of the leaf-level partitioned subtables, and data can be populated in parallel for each leaf-level partitioned subtable.

[0036] In this embodiment, the method for filling index data for the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtasks can be: first determine the number of tasks of the asynchronous subtasks, and then assign asynchronous subtasks to each leaf-level partition subtable in turn according to the set allocation rules and the number of tasks, and fill the data of the corresponding leaf-level partition subtable in the corresponding secondary index through the asynchronous subtasks.

[0037] Optionally, through the assigned asynchronous subtask, fill the index data for the secondary index of the corresponding leaf-level partitioned subtable in the horizontal partitioned table, including:

[0038] Determine the number of asynchronous subtasks;

[0039] According to the set allocation rules and the number of tasks, asynchronous subtasks are assigned to each leaf-level partition subtable in turn, and the data of the corresponding leaf-level partition subtable is filled in the corresponding secondary index through the asynchronous subtasks.

[0040] Among them, setting allocation rules can be understood as pre-set allocation rules. Setting allocation rules can be determined based on the asynchronous subtask free list of the execution environment created by the secondary index, or based on the asynchronous subtask usage list, or based on the execution status. This embodiment does not limit this.

[0041] It should be noted that the method for determining the number of tasks of asynchronous subtasks can be to determine the number of tasks of asynchronous subtasks by obtaining the number of tasks pre-specified by the user; or to determine the number of tasks of asynchronous subtasks based on the database operating environment, the number of sub-tables of the leaf-level partition sub-table in the horizontal partition table, and the amount of data in each leaf-level partition sub-table; this embodiment does not impose any restrictions here.

[0042] It should be noted that, according to the set allocation rules and the number of tasks, asynchronous subtasks are allocated to each leaf-level partition subtable in turn, and the data of the corresponding leaf-level partition subtable is filled in the corresponding secondary index through the asynchronous subtasks. The method is as follows: first, the initial information of the execution environment for creating the secondary index is obtained, and the initial information includes: an asynchronous subtask idle linked list with a length equal to the number of tasks, an asynchronous subtask use linked list with a length of 0, and an execution state with an initial value of normal state. A leaf-level partition subtable to be filled with index data is selected from the horizontal partition table as the current leaf-level partition subtable; and then when the current execution state is normal, In the normal state, the current asynchronous subtask node is determined from the asynchronous subtask free list for the current leaf-layer partition subtable, and the current asynchronous subtask node is added to the asynchronous subtask use list, and the selected operation of the current leaf-layer partition subtable is returned to continue until all leaf-layer partition subtables are assigned asynchronous subtasks; for each asynchronous subtask assigned and generated, the designated column data of the corresponding leaf-layer partition subtable is filled into the corresponding secondary index in order; finally, when the current asynchronous subtask completes the index data filling, the corresponding node of the current asynchronous subtask is removed from the asynchronous subtask use list and added back to the asynchronous subtask free list.

[0043] S130: After detecting that index data filling is completed for each leaf-level partition sub-table, the index state of the secondary index of the partition root table is set to a valid state to obtain the secondary index.

[0044] In this implementation, the index data of the secondary index of the corresponding leaf-level partition sub-table in the horizontal partition table is filled through the assigned asynchronous sub-task. After detecting that the index data filling of each leaf-level partition sub-table is completed, the index status of the secondary index of the partition root table is set to the valid status to obtain the secondary index.

[0045] In the technical solution provided by the embodiment of the present invention, after first receiving the secondary index creation instruction, the index status of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction is set to an invalid state; through the assigned asynchronous subtask, the index data is filled for the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table; after detecting that the index data filling of each leaf-level partition subtable is completed, the index status of the secondary index of the partition root table is set to a valid state to obtain the secondary index. In the embodiment of the present invention, the index data is filled for the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtask, which solves the problem of long time consumption when creating the secondary index, improves the efficiency of secondary index creation, and thus improves the user experience. Compared with the existing technology, the secondary index creation method for the horizontal partition table adopted solves the problem of inconsistency between the index data and the actual data in the partition table when the partition table data changes during the asynchronous index creation process of async asynchronous index creation, and solves the problem of high resource consumption when creating the index with parallel specifying the parallelism, thereby improving efficiency to a certain extent.

[0046] Optionally, before setting the index status of the secondary index of the partitioned root table to valid, the following steps are also included:

[0047] Destroy the execution environment used for secondary index creation;

[0048] Accordingly, after setting the index status of the secondary index of the partitioned root table to valid, the following steps are also included:

[0049] Update the partition table information corresponding to the horizontal partition table in the system table and clear the cache information of the horizontal partition table during database operation.

[0050] The execution environment can be understood as an environment for creating a secondary index. The execution environment related information may include an asynchronous subtask idle list, an asynchronous subtask usage list, a mutex, etc., which is not limited in this embodiment.

[0051] In this embodiment, before setting the index status of the secondary index of the partitioned root table to a valid state, the execution environment used for secondary index creation needs to be destroyed. Accordingly, after setting the index status of the secondary index of the partitioned root table to a valid state, the following steps are also performed: updating the partition table information corresponding to the horizontal partitioned table in the system table, and clearing the cached information of the horizontal partitioned table during database operation. For example, this could be the last modification time of the horizontal partitioned table. Furthermore, after the horizontal partitioned table information changes, the cached information of the horizontal partitioned table during database operation needs to be cleared. For example, since query operations on the horizontal partitioned table are allowed during index creation, if the execution plan of the query statement is cached, the newly created secondary index will not be used when the query statement is executed again. Only after the cache is cleared and the query statement is executed again will a new execution plan be reconstructed.

[0052] Example 2

[0053] Figure 2 This is a flowchart of a method for creating a secondary index for a horizontally partitioned table provided in the second embodiment of the present invention. This embodiment further refines the above embodiments. Specifically, it may include the following steps:

[0054] S210: After receiving the secondary index creation instruction, set the index status of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state.

[0055] S220, obtaining the number of tasks pre-specified by the user; or,

[0056] The number of asynchronous subtasks is determined based on the database operating environment, the number of leaf-level partition subtables in the horizontal partition table, and the amount of data in each leaf-level partition subtable.

[0057] The database operating environment may include information about the operating system, disk IO, memory, etc. where the database is located.

[0058] It should be noted that if the user specifies the number of asynchronous subtasks when creating an index, the specified number of asynchronous subtasks can be used directly. If the user does not specify the number of asynchronous subtasks when creating an index, the number of asynchronous subtasks must be determined based on a combination of factors, such as the database operating environment, the number of leaf-level partitioned subtables, and the amount of data in each leaf-level partitioned subtable. In this implementation, there is no limit on the number of asynchronous subtasks.

[0059] In this embodiment, when creating an index, it is necessary to consider the resource issues of the database operating environment and confirm the number of asynchronous subtasks based on the database operating environment. When creating an index asynchronously and in parallel, it is necessary to consider resource issues. Each leaf-level partition subtable needs to sort the data in its own partition subtable and fill it into the secondary index. Therefore, if resources are tight, there may be too many asynchronous subtasks, resulting in reduced execution efficiency. For example, when the asynchronous subtasks all read the leaf-level partition data and flush the secondary index data to the physical page, this will cause frequent disk read / write (I / O), which may cause an I / O bottleneck.

[0060] In this embodiment, when creating an index, the number of leaf-level partitioned subtables in a horizontally partitioned table must be considered. The number of asynchronous subtasks is determined based on the number of leaf-level partitioned subtables. The number of asynchronous subtasks should be less than or equal to the number of leaf-level partitioned subtables. Each leaf-level partitioned subtable uses a single asynchronous subtask to populate secondary index data. Excessive asynchronous subtasks are meaningless.

[0061] In this embodiment, when creating an index, the data volume of the leaf-level partitioned sub-tables needs to be taken into consideration, and the number of asynchronous sub-tasks is determined based on the data volume of the leaf-level partitioned sub-tables. For example, when the data volume of each partitioned sub-table is too large, it is necessary to consider filling the index data for each partitioned sub-table in batches. This is because if the data volume of the sub-table is too large, the database operating environment may not be able to support asynchronous parallel sorting and data filling for all leaf-level partitioned sub-tables at the same time. In this case, it is necessary to consider filling the index data for each partitioned sub-table in batches.

[0062] S230. Acquire initial information of the execution environment for creating a secondary index. The initial information includes: an asynchronous subtask idle linked list whose length is equal to the number of tasks, an asynchronous subtask use linked list whose length is 0, and an execution state whose initial value is a normal state.

[0063] It should be noted that, based on the number of asynchronous subtasks, the execution environment for initializing the creation of the secondary index may include: an asynchronous subtask free list with a length equal to the number of tasks, an asynchronous subtask use list with a length of 0, and may also include execution status, mutexes, etc., which are not limited in this embodiment. Among them, the initial value of the length of the asynchronous subtask free list is equal to the number of asynchronous subtasks; the initial value of the length of the asynchronous subtask use list is 0; among them, the execution status can be understood as the execution status of the asynchronous parallel creation of the horizontal partition table secondary index, which may include normal status and abnormal status, and its initial value is normal status; the mutex can be used to ensure that the main task and the asynchronous subtask can work together normally to complete the work. When the main task and the asynchronous subtask access the same resource at the same time, mutex protection is required.

[0064] S240: Select a leaf-level partition sub-table to be populated with index data from the horizontal partition table as the current leaf-level partition sub-table.

[0065] The current leaf-layer partition sub-table may be understood as the leaf-layer partition sub-table currently to be filled with index data.

[0066] In this embodiment, after obtaining the initial information of the execution environment for creating the secondary index, a leaf-level partition sub-table to be populated with index data is selected from the horizontal partition table as the current leaf-level partition sub-table.

[0067] S250. When the current execution state is normal, determine the current asynchronous subtask node from the asynchronous subtask free list for the current leaf-level partition subtable, add the current asynchronous subtask node to the asynchronous subtask usage list, and return to continue executing the selected operation of the current leaf-level partition subtable until all leaf-level partition subtables are assigned asynchronous subtasks.

[0068] An asynchronous subtask node can be understood as a node in a linked list that stores asynchronous subtask information. A node in a linked list can be understood as a non-contiguous, non-sequential storage structure on a physical storage unit. The logical order of data elements is achieved through the order of pointer links in the linked list. Each element in a linked list is called a node. A linked list consists of one or more nodes, and nodes store asynchronous subtask information. Asynchronous subtasks can be generated dynamically at runtime.

[0069] In this embodiment, the main task can be understood as a main process or a main thread. The main task is a task that generates other asynchronous subtasks, and the subtask can be understood as a subprocess or a subthread.

[0070] It should be noted that when the current execution state is normal, it indicates that both the main task and the asynchronous subtask are in normal state. At this time, the current asynchronous subtask node can be determined from the asynchronous subtask idle list for the current leaf-level partition subtable and added to the asynchronous subtask use list. In this embodiment, the current execution state can include normal state and abnormal state, with the initial state being normal state.

[0071] In this embodiment, the method for determining the current asynchronous subtask node from the asynchronous subtask free list for the current leaf-layer partition subtable can be to determine whether there is an idle asynchronous subtask node. If there is an idle asynchronous subtask node in the asynchronous subtask free list, one of the idle asynchronous subtask nodes is selected as the current asynchronous subtask node; otherwise, the main task periodically monitors the length of the asynchronous subtask free list and waits for the execution of other asynchronous subtasks to be completed before allocating an asynchronous subtask to the current leaf-layer partition subtable.

[0072] Optionally, the current leaf-level partition subtable determines the current asynchronous subtask node from the asynchronous subtask idle list, including:

[0073] If the length of the asynchronous subtask free list is greater than 0, one is selected as the current asynchronous subtask node; otherwise, the main task regularly monitors the length of the asynchronous subtask free list and waits for the completion of other asynchronous subtasks before allocating an asynchronous subtask to the current leaf-layer partition subtable.

[0074] In this embodiment, if the length of the asynchronous subtask free list is greater than 0, one is selected as the current asynchronous subtask node; otherwise, the main task needs to regularly monitor the length of the asynchronous subtask free list and wait for the execution of other asynchronous subtasks to be completed before allocating asynchronous subtasks to the current leaf-layer partition subtable.

[0075] S260: For each asynchronous subtask that is assigned and generated, fill the designated column data of the corresponding leaf-level partitioned subtable into the corresponding secondary index in order.

[0076] In this embodiment, the main task applies for asynchronous subtasks for each leaf-level partitioned subtable to populate secondary index data. When applying for an asynchronous subtask for a leaf-level partitioned subtable, the main task first retrieves a current asynchronous subtask node from the asynchronous subtask free list and adds it to the asynchronous subtask in-use list. When an asynchronous subtask is completed, the corresponding node is removed from the asynchronous subtask in-use list and added to the asynchronous subtask free list.

[0077] It should be noted that after all leaf-level partition sub-tables are assigned asynchronous sub-tasks, the main task enters a waiting state and periodically checks whether all assigned asynchronous sub-tasks have been completed, that is, whether the length of the linked list used by the asynchronous sub-tasks is 0. If it is 0, it means that all asynchronous sub-tasks have been executed and completed, and subsequent operations can be performed at this time.

[0078] In this embodiment, the current asynchronous subtask populates the data in the current leaf-level partitioned subtable into the corresponding secondary index according to the set filling rules. When filling data according to the set filling rules, the main task and the asynchronous subtask need to work together to complete it. During the process of filling the secondary index data of the current leaf-level partitioned subtable according to the set filling rules, the execution status needs to be monitored regularly.

[0079] It should be noted that for each asynchronous subtask assigned and generated, the method for orderly filling the specified column data of the corresponding leaf-level partitioned subtable into the corresponding secondary index can be: first, through the current asynchronous subtask, orderly fill the specified column data of the current leaf-level partitioned subtable into the corresponding secondary index, and then during the data filling process of the secondary index, regularly monitor the execution status. If it is an abnormal state, terminate the current asynchronous subtask in advance; if it is a normal state, continue to fill data until the data filling is completed; if the current asynchronous subtask itself is abnormal during the secondary index data filling process, terminate the current asynchronous subtask and adjust the execution status to an abnormal state.

[0080] Optionally, for each assigned and generated asynchronous subtask, the data of the specified columns of the corresponding leaf-level partitioned subtable is sequentially populated into the corresponding secondary index, including:

[0081] Through the current asynchronous subtask, the specified column data of the current leaf-level partitioned subtable is sequentially filled into the corresponding secondary index;

[0082] During the data filling process of the secondary index, the execution status is monitored regularly. If it is abnormal, the current asynchronous subtask is terminated in advance. If it is normal, the data filling continues until the data filling is completed.

[0083] If the current asynchronous subtask itself is abnormal during the secondary index data filling process, the current asynchronous subtask will be terminated and the execution status will be adjusted to the abnormal state.

[0084] Among them, regular monitoring of the execution status can be used to detect whether the index data filling status is normal.

[0085] In this embodiment, the data filling status may include abnormal index data filling and normal index data filling.

[0086] In this embodiment, filling the secondary index of the leaf-level partitioned subtable requires the collaboration of the main task and the asynchronous subtask. During the index data filling process, it is necessary to regularly check whether the data filling status is normal. If an exception occurs in the current asynchronous subtask during the secondary index data filling process, the current asynchronous subtask is terminated and the execution state is adjusted from normal to abnormal.

[0087] It should be noted that when the index data is filled with exceptions and adjusted to an abnormal state, if the main task detects that the execution status is an abnormal state, all executing asynchronous subtasks will be stopped and an error will be reported; if the asynchronous subtask detects that the execution status is an abnormal state, the current asynchronous subtask will be ended early.

[0088] S270: When the current asynchronous subtask completes filling the index data, the node corresponding to the current asynchronous subtask is removed from the asynchronous subtask usage linked list and added back to the asynchronous subtask idle linked list.

[0089] It should be noted that the process of filling index data for the secondary index will continuously select the leaf partition sub-table to be filled with index data from the horizontal partition table and assign asynchronous sub-tasks to it. After each asynchronous sub-task is assigned, the next leaf partition sub-table to be filled with index data will be selected and assigned an asynchronous sub-task.

[0090] In this embodiment, when the current asynchronous subtask completes filling the index data, the node corresponding to the current asynchronous subtask needs to be removed from the asynchronous subtask use list and added back to the asynchronous subtask idle list.

[0091] S280: After detecting that index data is filled in each leaf-level partition sub-table, the index state of the secondary index of the partition root table in the horizontal partition table is set to a valid state to obtain the secondary index.

[0092] In this embodiment, after detecting that index data filling is completed in each leaf-level partition sub-table, the index state of the secondary index of the partition root table in the horizontal partition table is set to a valid state to obtain the secondary index.

[0093] In the technical solution provided by the embodiment of the present invention, after receiving the secondary index creation instruction, the index status of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction is set to an invalid state, and then the number of tasks pre-specified by the user is obtained; or, according to the database operating environment, the number of leaf-level partition sub-tables in the horizontal partition table and the amount of data in each leaf-level partition sub-table, the number of tasks of the asynchronous sub-task is determined, the initial information of the execution environment for secondary index creation is obtained, and then a leaf-level partition sub-table to be filled with index data is selected from the horizontal partition table as the current leaf-level partition sub-table; when the current execution state is normal, the current leaf-level partition sub-table is determined from the asynchronous sub-task free list. The current asynchronous subtask node is determined, and the current asynchronous subtask node is added to the asynchronous subtask usage linked list, and the selected operation of the current leaf-level partition subtable is returned to continue execution until all leaf-level partition subtables are assigned asynchronous subtasks; for each asynchronous subtask assigned and generated, the designated column data of the corresponding leaf-level partition subtable is sequentially filled into the corresponding secondary index; when the current asynchronous subtask completes the index data filling, the corresponding node of the current asynchronous subtask is removed from the asynchronous subtask usage linked list and re-added to the asynchronous subtask free linked list. After detecting that each leaf-level partition subtable has completed the index data filling, the index status of the secondary index of the partition root table in the horizontal partition table is set to a valid state to obtain the secondary index. The embodiment of the present invention, by determining the current asynchronous subtask node from the asynchronous subtask free linked list for the current leaf-level partition subtable when the current execution state is normal, and for each asynchronous subtask assigned and generated, the designated column data of the corresponding leaf-level partition subtable is sequentially filled into the corresponding secondary index, further improves the efficiency of secondary index creation, thereby improving user experience.

[0094] Optionally, when an error occurs during the execution of the main task or any asynchronous subtask, the corresponding execution state is adjusted from a normal state to an abnormal state; accordingly, when the current execution state is an abnormal state, the following is also included:

[0095] When any asynchronous subtask being executed detects an abnormal state, the current asynchronous subtask is terminated;

[0096] When the main task detects an abnormal state, it stops all executing asynchronous subtasks and ends with an error.

[0097] In this embodiment, if an error occurs during the execution of the main task or any asynchronous subtask, the corresponding execution state is adjusted from a normal state to an abnormal state. When any asynchronous subtask being executed detects an abnormal state, the current asynchronous subtask is terminated; when the main task detects an abnormal state, all asynchronous subtasks being executed are stopped and an error is reported. For example, the main task may have an abnormality due to a user disconnection, or it may be due to a situation where the client forcibly disconnects the connection session when the server execution time is too long; this embodiment does not impose any restrictions on this. The asynchronous subtask may have an abnormality due to an index expression calculation error, a column sorting error, insufficient space, etc., which are not imposed any restrictions on this embodiment.

[0098] It should be noted that if an exception occurs in the current main task, the execution status will be adjusted to an abnormal state and the task will end with an error. Before the main task ends with an error, exception handling will be performed on the erroneous statement. The exception handling for asynchronous parallel creation of secondary indexes on horizontally partitioned tables is to stop all related asynchronous subtasks in progress. While the main task is waiting for the asynchronous subtask to end, it adjusts to a waiting state and monitors the length of the linked list used by the asynchronous subtasks at a set period. When the length of the linked list used by the asynchronous subtask is 0, it is considered that all related asynchronous subtasks in progress have ended. Only then can the subsequent error termination operation be executed.

[0099] In this embodiment, after all leaf-level partitioned subtables have successfully applied for asynchronous subtasks to populate secondary index data, the main task is adjusted to a waiting state and periodically checks whether all asynchronous subtasks have completed. That is, whether the length of the linked list used by the asynchronous subtasks is 0. If it is 0, it means that all asynchronous subtasks have completed execution, and subsequent operations can be performed at this time. During the waiting process, if the main task finds that the execution status is abnormal, it waits until all asynchronous subtasks have completed execution and released resources, and then reports an error to the user and returns.

[0100] For example, to better understand the method for creating a secondary index for a horizontally partitioned table, Figure 3 This is a flowchart of a method for creating a secondary index for a horizontal partition table provided in the second embodiment of the present invention. Figure 3 As shown in the figure, in actual scenarios, the process of creating a secondary index for a horizontally partitioned table is as follows:

[0101] S301: After receiving a secondary index creation instruction, set the index status of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state.

[0102] S302: Confirm the number of subtasks for asynchronously executing SQL.

[0103] S303: Initialize the asynchronous parallel creation environment of the secondary index of the horizontal partition table on the session.

[0104] S304: Continue to check whether there is a leaf-level partition sub-table to be filled with data. If so, execute step S305; if not, execute step S309.

[0105] S305: Take out a single leaf-level partition sub-table.

[0106] S306: Is there an idle asynchronous subtask? If yes, execute step S308; if not, execute step S307.

[0107] S307: Wait for an idle asynchronous subtask and regularly check whether there is an idle asynchronous subtask.

[0108] S308: The asynchronous subtask fills the secondary index data for the leaf-layer partition subtable and regularly checks the execution status.

[0109] S3081: Check whether the data in the secondary index of the leaf-level partitioned sub-table is normal. If so, execute S3082; if not, execute S3084.

[0110] S3082. Regularly check whether the execution status is normal. If so, execute S3083; if not, execute S3084.

[0111] S3083. Continue filling the secondary index data and wait for the secondary index data to be filled. After completion, execute S3085.

[0112] S3084. End the current asynchronous subtask in advance and adjust the asynchronous execution state to an abnormal state.

[0113] S3085. End the current asynchronous subtask normally.

[0114] S309: Wait for all asynchronous subtasks to complete.

[0115] S3091. Regularly check whether the execution status is normal. If so, wait for all asynchronous subtasks to complete, and then execute S310; if not, execute S312.

[0116] S310: Destroy the asynchronous parallel execution environment of the secondary index of the horizontal partition table on the session, and update the index status of the secondary index of the partition root table in the horizontal partition table to a valid state.

[0117] S311. Successfully returns to the user.

[0118] S312: Report an error to the user.

[0119] For example, the performance test conclusions of asynchronous parallel creation of a single index on a horizontally partitioned table are shown in the following table: As the number of asynchronous subtasks increases, the performance of asynchronous parallel index creation gradually improves. However, after reaching the optimal value, the performance begins to decline as the number of asynchronous subtasks increases. Therefore, it is very important to reasonably select the number of asynchronous subtasks based on comprehensive factors such as the database operating environment, the number of leaf-level partitioned subtables, and the amount of data in the leaf-level partitioned subtables.

[0120]

[0121]

[0122] Example 3

[0123] Figure 4 This is a schematic diagram of the structure of a device for creating a secondary index for a horizontal partition table provided in the third embodiment of the present invention. The device for creating a secondary index for a horizontal partition table provided in this embodiment can be implemented by software and / or hardware and can be configured in a server to implement a method for creating a secondary index for a horizontal partition table in the embodiment of the present invention. Figure 4 As shown, the device may specifically include: a state setting module 410, a data filling module 420 and an index obtaining module 430.

[0124] The state setting module 410 is configured to set the index state of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state after receiving the secondary index creation instruction.

[0125] The data filling module 420 is configured to fill index data on the secondary index of the corresponding leaf-level partition sub-table in the horizontal partition table through the assigned asynchronous sub-task.

[0126] The index obtaining module 430 is configured to set the index status of the secondary index of the partition root table to a valid state after detecting that each leaf-level partition sub-table has completed filling the index data, thereby obtaining the secondary index.

[0127] In the technical solution provided by the embodiment of the present invention, first, after the state setting module receives the secondary index creation instruction, it sets the index status of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state; then the data filling module fills the index data of the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtask; after the index acquisition module detects that the index data filling of each leaf-level partition subtable is completed, it sets the index status of the secondary index of the partition root table to a valid state to obtain the secondary index. The embodiment of the present invention solves the problem of long time consumption when creating the secondary index by filling the index data of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtask, thereby improving the efficiency of secondary index creation and thus improving the user experience.

[0128] Optionally, the device for creating a secondary index of a horizontal partition table further includes:

[0129] The environment destruction module is used to destroy the execution environment used for creating the secondary index before setting the index state of the secondary index of the partition root table to a valid state.

[0130] Correspondingly, the device for creating a secondary index of a horizontal partition table further includes:

[0131] The information cleaning module is used to update the partition table information corresponding to the horizontal partition table in the system table after setting the index status of the secondary index of the partition root table to a valid state, and clean up the cache information of the horizontal partition table during the database operation.

[0132] Optionally, the data filling module 420 may include:

[0133] The subtask generation unit is used to determine the number of asynchronous subtasks.

[0134] The data filling unit is used to assign asynchronous subtasks to each of the leaf-level partition subtables in turn according to the set allocation rules and the number of tasks, and fill the data of the corresponding leaf-level partition subtable in the corresponding secondary index through the asynchronous subtasks.

[0135] Optionally, a subtask generating unit is used to obtain the number of tasks pre-specified by the user; or,

[0136] The number of asynchronous subtasks is determined according to the database operating environment, the number of leaf-level partition subtables in the horizontal partition table, and the data volume of each leaf-level partition subtable.

[0137] Optionally, the data filling unit may include:

[0138] The information acquisition subunit is used to obtain the initial information of the execution environment for creating a secondary index, wherein the initial information includes: an asynchronous subtask idle list with a length equal to the number of tasks, an asynchronous subtask usage list with a length of 0, and an execution state with an initial value of normal state.

[0139] The sub-table selection sub-unit is used to select a leaf-level partition sub-table to be filled with index data from the horizontal partition table as the current leaf-level partition sub-table.

[0140] The subtask node completion unit is used to determine the current asynchronous subtask node from the asynchronous subtask free list for the current leaf-layer partition subtable when the current execution state is normal, and add the current asynchronous subtask node to the asynchronous subtask usage list, and return to continue executing the selected operation of the current leaf-layer partition subtable until all leaf-layer partition subtables are assigned asynchronous subtasks.

[0141] The index filling sub-unit is used to sequentially fill the specified column data of the corresponding leaf-level partitioned sub-table into the corresponding secondary index for each assigned and generated asynchronous sub-task.

[0142] The linked list re-adding subunit is used to remove the node corresponding to the current asynchronous subtask from the asynchronous subtask use linked list and re-add it to the asynchronous subtask idle linked list when the current asynchronous subtask completes filling of index data.

[0143] Optionally, when an error occurs during the execution of the main task or any asynchronous subtask, the corresponding execution state is adjusted from a normal state to an abnormal state; accordingly, when the current execution state is an abnormal state, the following is also included:

[0144] The subtask exception handling unit is used to terminate the current asynchronous subtask when any asynchronous subtask being executed detects the abnormal state.

[0145] The main task exception handling unit is used to stop all asynchronous subtasks being executed and report an error when the main task detects the abnormal state.

[0146] Optionally, the linked list can be re-added with subunits, which can be used to:

[0147] If the length of the asynchronous subtask free list is greater than 0, one is selected as the current asynchronous subtask node; otherwise, the main task regularly monitors the length of the asynchronous subtask free list and waits for the execution of other asynchronous subtasks to be completed before allocating asynchronous subtasks to the current leaf-layer partition subtable.

[0148] Optionally, the index fill subunit can be used to:

[0149] Through the current asynchronous subtask, the data in the current leaf-layer partition subtable is filled into the corresponding secondary index.

[0150] Through the current asynchronous subtask, the designated column data of the current leaf-level partition subtable is sequentially filled into the corresponding secondary index.

[0151] During the data filling process of the secondary index, the execution status is monitored regularly. If it is an abnormal state, the current asynchronous subtask is terminated in advance. If it is a normal state, the data is continued to be filled until the data filling is completed.

[0152] If the current asynchronous subtask itself is abnormal during the filling process of the secondary index data, the current asynchronous subtask is terminated and the execution state is adjusted to an abnormal state.

[0153] The apparatus for creating a secondary index for a horizontal partitioned table provided in an embodiment of the present invention can execute the method for creating a secondary index for a horizontal partitioned table provided in any embodiment of the present invention, and has corresponding functional modules and beneficial effects of the execution method.

[0154] Example 4

[0155] Figure 5 A schematic diagram of the structure of a computer device provided in the fourth embodiment of the present invention is shown in FIG. Figure 5 As shown, the computer device includes a processor 510, a memory 520, an input device 530, and an output device 540; the number of processors 510 in the computer device can be one or more. Figure 5 In the figure, a processor 510 is used as an example; the processor 510, memory 520, input device 530 and output device 540 in the computer device can be connected via a bus or other means. Figure 5 The bus connection is taken as an example.

[0156] The memory 520, as a computer-readable storage medium, can be used to store software programs, computer-executable programs, and modules, such as the program instructions / modules corresponding to the method for creating a secondary index for a horizontally partitioned table in the embodiments of the present invention (for example, the state setting module 410, the data filling module 420, and the index obtaining module 430 in the apparatus for creating a secondary index for a horizontally partitioned table). The processor 510 executes the software programs, instructions, and modules stored in the memory 520 to execute various functional applications and data processing of the computer device, thereby implementing the aforementioned method for creating a secondary index for a horizontally partitioned table.

[0157] The memory 520 may primarily include a program storage area and a data storage area. The program storage area may store an operating system and at least one application required for a function; the data storage area may store data created based on the use of the terminal, etc. Furthermore, the memory 520 may include high-speed random access memory and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state memory device. In some instances, the memory 520 may further include memory remotely located relative to the processor 510, and these remote memories may be connected to the computer device via a network. Examples of the aforementioned networks include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0158] The input device 530 may be used to receive input digital or character information and generate key signal input related to user settings and function control of the computer device. The output device 540 may include a display device such as a display screen.

[0159] Example 5

[0160] A fifth embodiment of the present invention further provides a storage medium containing computer-executable instructions. When the computer-executable instructions are executed by a computer processor, the computer-executable instructions are used to execute a method for creating a secondary index for a horizontally partitioned table. The method includes:

[0161] After receiving the secondary index creation instruction, the index state of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction is set to an invalid state.

[0162] The secondary index of the corresponding leaf-level partition sub-table in the horizontal partition table is filled with index data through the assigned asynchronous sub-task.

[0163] After detecting that each leaf-level partition sub-table has completed filling the index data, the index state of the secondary index of the partition root table is set to a valid state to obtain the secondary index.

[0164] Of course, the computer executable instructions of the storage medium provided by the embodiment of the present invention are not limited to the operations of the method described above, and can also execute related operations in the method for creating a secondary index for a horizontally partitioned table provided by any embodiment of the present invention.

[0165] Through the above description of the implementation methods, those skilled in the art can clearly understand that the present invention can be implemented with the help of software and necessary general-purpose hardware, and of course it can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as a computer floppy disk, read-only memory (ROM), random access memory (RAM), flash memory (FLASH), hard disk or optical disk, etc., including a number of instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute the methods described in each embodiment of the present invention.

[0166] It is worth noting that in the embodiment of the above-mentioned search device, the various units and modules included are only divided according to functional logic, but are not limited to the above-mentioned division, as long as the corresponding functions can be achieved; in addition, the specific names of the functional units are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of the present invention.

[0167] Note that the above are only preferred embodiments of the present invention and the technical principles employed. Those skilled in the art will understand that the present invention is not limited to the specific embodiments described herein, and that various obvious changes, readjustments, and substitutions can be made by those skilled in the art without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in detail through the above embodiments, the present invention is not limited to the above embodiments and may include many other equivalent embodiments without departing from the concept of the present invention. The scope of the present invention is determined by the scope of the appended claims.

Claims

1. A method for creating a secondary index for a horizontal partitioned table, characterized in that: include: After receiving the secondary index creation instruction, the index state of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction is set to an invalid state; Filling index data for the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtask; After detecting that index data filling is completed for each leaf-level partition sub-table, the index state of the secondary index of the partition root table is set to a valid state to obtain the secondary index; The method of filling index data for the secondary index of the corresponding leaf-level partition subtable in the horizontal partition table through the assigned asynchronous subtask includes: Determine the number of asynchronous subtasks; Allocate asynchronous subtasks to each leaf-layer partition subtable in sequence according to the set allocation rule and the number of tasks, and fill the data of the corresponding leaf-layer partition subtable into the corresponding secondary index through the asynchronous subtasks; The step of allocating asynchronous subtasks to each leaf-layer partition subtable in accordance with the set allocation rule and the number of tasks, and filling the data of the corresponding leaf-layer partition subtable in the corresponding secondary index through the asynchronous subtasks, includes: Obtaining initial information of an execution environment for creating a secondary index, the initial information including: an asynchronous subtask idle linked list with a length equal to the number of tasks, an asynchronous subtask usage linked list with a length of 0, and an execution state with an initial value of normal; Select a leaf-level partition sub-table to be populated with index data from the horizontal partition table as the current leaf-level partition sub-table; When the current execution state is normal, determining the current asynchronous subtask node from the asynchronous subtask free linked list for the current leaf-layer partition subtable, adding the current asynchronous subtask node to the asynchronous subtask use linked list, and returning to continue executing the selected operation of the current leaf-layer partition subtable until all leaf-layer partition subtables are assigned asynchronous subtasks; For each asynchronous subtask that is assigned and generated, the data of the specified columns of the corresponding leaf-level partitioned subtable is sequentially filled into the corresponding secondary index; When the asynchronous subtask completes filling the index data, the node corresponding to the current asynchronous subtask is removed from the asynchronous subtask use linked list and added back to the asynchronous subtask idle linked list.

2. The method according to claim 1, characterized in that Determining the number of asynchronous subtasks includes: Get the number of tasks specified by the user; or, The number of asynchronous subtasks is determined according to the database operating environment, the number of leaf-level partition subtables in the horizontal partition table, and the data volume of each leaf-level partition subtable.

3. The method according to claim 2, characterized in that When an error occurs in the execution of the main task or any asynchronous subtask, the corresponding execution state is adjusted from normal to abnormal; Correspondingly, when the current execution state is abnormal, it also includes: When any asynchronous subtask being executed detects the abnormal state, the current asynchronous subtask is terminated; When the main task detects the abnormal state, it stops all the asynchronous subtasks being executed and ends with an error report.

4. The method according to claim 1, wherein Determining the current asynchronous subtask node from the asynchronous subtask idle linked list for the current leaf-layer partition subtable includes: If the length of the asynchronous subtask free list is greater than 0, one is selected as the current asynchronous subtask node; otherwise, the main task regularly monitors the length of the asynchronous subtask free list and waits for the execution of other asynchronous subtasks to be completed before allocating asynchronous subtasks to the current leaf-layer partition subtable.

5. The method according to claim 1, wherein For each asynchronous subtask that is assigned and generated, the designated column data of the corresponding leaf-level partitioned subtable is sequentially filled into the corresponding secondary index, including: Through the current asynchronous subtask, the specified column data of the current leaf-level partition subtable is sequentially filled into the corresponding secondary index; During the data filling process of the secondary index, the execution status is monitored regularly. If it is an abnormal state, the current asynchronous subtask is terminated in advance; if it is a normal state, the data is continued to be filled until the data filling is completed; If the current asynchronous subtask itself is abnormal during the filling process of the secondary index data, the current asynchronous subtask is terminated and the execution state is adjusted to an abnormal state.

6. The method according to any one of claims 1 to 5, characterized in that Before setting the index status of the secondary index of the partitioned root table to a valid state, the method further includes: Destroy the execution environment used for secondary index creation; Correspondingly, after setting the index status of the secondary index of the partition root table to a valid state, the method further includes: Update the partition table information corresponding to the horizontal partition table in the system table, and clear the cache information of the horizontal partition table during the database operation process.

7. A device for creating a secondary index of a horizontal partition table, characterized in that: include: A state setting module is configured to, upon receiving a secondary index creation instruction, set the index state of the secondary index of the partition root table in the horizontal partition table corresponding to the instruction to an invalid state; A data filling module is used to fill index data for the secondary index of the corresponding leaf-level partition sub-table in the horizontal partition table through the assigned asynchronous sub-task; An index obtaining module, configured to set the index state of the secondary index of the partition root table to a valid state after detecting that the index data of each leaf-level partition sub-table has been filled, and obtain the secondary index; Among them, the data filling module includes: A subtask generation unit, used to determine the number of asynchronous subtasks; A data filling unit is used to assign asynchronous subtasks to each of the leaf-layer partition subtables in sequence according to the set allocation rules and the number of tasks, and fill the data of the corresponding leaf-layer partition subtable in the corresponding secondary index through the asynchronous subtasks; The data filling unit includes: An information acquisition subunit is configured to acquire initial information of an execution environment for creating a secondary index, wherein the initial information includes: an asynchronous subtask idle linked list having a length equal to the number of tasks, an asynchronous subtask use linked list having a length of 0, and an execution state having an initial value of normal; The sub-table selection sub-unit is used to select a leaf-level partition sub-table to be populated with index data from the horizontal partition table as the current leaf-level partition sub-table; a subtask node completion unit, configured to, when the current execution state is normal, determine a current asynchronous subtask node from the asynchronous subtask free linked list for the current leaf-layer partition subtable, add the current asynchronous subtask node to the asynchronous subtask use linked list, and return to continue executing the selected operation of the current leaf-layer partition subtable until all leaf-layer partition subtables are assigned asynchronous subtasks; The index filling sub-unit is used to divide the corresponding leaf layer for each asynchronous sub-task that is assigned and generated; the specified column data of the sub-table is sequentially filled into the corresponding secondary index; The linked list re-adding subunit is used to remove the node corresponding to the current asynchronous subtask from the asynchronous subtask use linked list and re-add it to the asynchronous subtask idle linked list when the current asynchronous subtask completes filling of index data.

8. A computer device, characterized in that: The computer device comprises: one or more processors; a memory for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method for creating a secondary index for a horizontal partitioned table as described in any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for creating a secondary index for a horizontal partitioned table according to any one of claims 1 to 6 is implemented.

Images