Parallel insertion method and device for insert select in database transaction

Abstract

The invention relates to a parallel insertion method and device for an insert select in a database transaction.The method comprises the steps that when an insert select request sent by a terminal is received, data in a source table are split into different data intervals through a main thread of a database, and the insert select request is used for copying the data in the source table into a target table; sub-threads with the same number as the number of the data intervals are created through the main thread, and the main thread is also used as a sub-thread; the data in each data interval is distributed to one sub-thread, the multiple sub-threads are controlled to concurrently execute insertion operation on the target table, and each sub-thread is used for inserting the data in the corresponding data interval into the target table in a serial mode. The database insertion performance is improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a parallel insertion method and device for insert select within a database transaction. Background Art

[0002] The insert select syntax allows you to select data from one table and insert it into another table. This feature is very suitable for situations where you need to copy data from one table to another, or store query results in a new table. The insert select import is implemented by querying the source table data and inserting it into the target table row by row, that is, insert select inserts data in serial mode. The import rate of insert select is proportional to the amount of data in the source table. When the source table data is huge, data import takes a lot of time, resulting in low data import performance in the database. Summary of the invention

[0003] The present application provides a parallel insertion method and device for insert select within a database transaction to solve the problem of low data import performance in the database.

[0004] In a first aspect, the present application provides a parallel insertion method for insert select in a database, the method comprising:

[0005] When receiving an insert select request sent by a terminal, splitting the data in the source table into different data intervals through the main thread of the database, wherein the insert select request is used to copy the data in the source table to the target table;

[0006] Creating, by the main thread, child threads having the same number as the number of data intervals, wherein the main thread also serves as a child thread;

[0007] The data in each of the data intervals is assigned to a sub-thread, and the multiple sub-threads are controlled to concurrently perform insert operations on the target table, wherein each sub-thread is used to serially insert the data in the corresponding data interval into the target table.

[0008] Optionally, before splitting the data in the source table into different data intervals through the main thread of the database, the method further includes:

[0009] Acquire the amount of data in the source table and the system resources of the database through the main thread of the database;

[0010] Determining the degree of parallelism of parallel data insertion according to the data volume and the system resources;

[0011] Using a database histogram to analyze the data distribution in the source table to obtain multiple initial data intervals;

[0012] If the quantity difference between the parallelism and the initial data interval is less than the set difference, the initial data interval is adjusted according to the parallelism to obtain a final data interval.

[0013] Optionally, after obtaining a plurality of initial data intervals, the method further includes:

[0014] If the difference between the degree of parallelism and the initial data interval is greater than or equal to a set difference, the initial data interval is used as the final data interval.

[0015] Optionally, in the process of using a database histogram to analyze the data distribution in the source table to obtain a plurality of initial data intervals, the method further includes:

[0016] Detect in real time whether data operations occur in the source table;

[0017] If data manipulation is detected, the adjusted data distribution is analyzed again using the database histogram to obtain an adjusted data interval.

[0018] Optionally, the database is a distributed database, and splitting the data in the source table into different data intervals through the main thread of the database includes:

[0019] If no database histogram is created in the source table, query all data nodes in the distributed database;

[0020] Counting the total amount of data of all the data nodes;

[0021] The total data volume is divided into equal parts according to the degree of parallelism, and each segment of the divided data is inserted into a data interval.

[0022] Optionally, controlling multiple sub-threads to concurrently perform insert operations on the target table includes:

[0023] Controlling multiple sub-threads to concurrently insert data in corresponding data intervals in the source table into the target table, wherein the multiple sub-threads are in one transaction and the data insertion operation can be revoked;

[0024] Controlling multiple sub-threads to submit data, wherein the data submission is used to permanently save the data in the source table to the target table.

[0025] Optionally, the database is a distributed database, and before controlling multiple sub-threads to submit data, the method further includes:

[0026] If a query operation on the setting data in the source table is detected, determining the state of the setting transaction in the distributed database where the setting data is located;

[0027] If the set transaction is in an active state, querying the data in the set transaction is prohibited, wherein the active state is used to indicate that at least some of the sub-threads fail to submit;

[0028] If the set transaction is in an inactive state, querying the data in the set transaction is allowed, wherein the inactive state is used to indicate that all sub-threads are submitted successfully.

[0029] In a second aspect, the present application provides a parallel insertion device for insert select in a database transaction, the device comprising:

[0030] A splitting module, used for splitting the data in the source table into different data intervals through the main thread of the database when receiving an insert select request sent by the terminal, wherein the insert select request is used to copy the data in the source table to the target table;

[0031] A creation module, used for creating, through the main thread, sub-threads having the same number as the number of data intervals, wherein the main thread also serves as a sub-thread;

[0032] The insertion module is used to assign the data in each data interval to a sub-thread and control multiple sub-threads to concurrently perform insertion operations on the target table, wherein each sub-thread is used to serially insert the data in the corresponding data interval into the target table.

[0033] In a third aspect, the present application provides an electronic device comprising: at least one communication interface; at least one bus connected to the at least one communication interface; at least one processor connected to the at least one bus; and at least one memory connected to the at least one bus.

[0034] In a fourth aspect, the present application further provides a computer storage medium storing computer executable instructions, wherein the computer executable instructions are used to execute the parallel insertion method of insert select within a database transaction described in any one of the above items of the present application.

[0035] The above technical solution provided by the embodiment of the present application has the following advantages compared with the prior art: the source table data is split into multiple data intervals, and concurrent insertion operations are performed on the data in the multiple data intervals through multiple sub-threads, so as to realize the concurrent insertion of the data in the source table into the target table. Compared with the serial insertion in the prior art, the parallel insertion in the present application improves the efficiency of insert select in the database and improves the data import performance in the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0037] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0038] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0039] Figure 1 A system schematic diagram of parallel insertion of insert select within a database transaction provided by an embodiment of the present application;

[0040] Figure 2 A flow chart of a method for parallel insertion of insert select within a database transaction provided in an embodiment of the present application;

[0041] Figure 3 A parallel insertion flow chart of insert select in a database transaction provided in an embodiment of the present application;

[0042] Figure 4 A schematic diagram of the structure of a parallel insertion device for insert select in a database transaction provided by an embodiment of the present application;

[0043] Figure 5 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0045] The disclosure below provides many different embodiments or examples to implement different structures of the present invention. In order to simplify the disclosure of the present invention, the parts and settings of specific examples are described below. Of course, they are only examples, and the purpose is not to limit the present invention. In addition, the present invention can repeat reference numbers and / or letters in different examples. This repetition is for the purpose of simplification and clarity, and does not itself indicate the relationship between the various embodiments and / or settings discussed.

[0046] In order to solve the problem mentioned in the background technology, according to one aspect of an embodiment of the present application, an embodiment of a parallel insertion method of insert select within a database transaction is provided.

[0047] Optionally, in an embodiment of the present application, the above-mentioned parallel insertion method of insert select in a database transaction can be applied to the following: Figure 1 In the hardware environment composed of the terminal 101 and the server 103 shown in FIG. Figure 1 As shown, the server 103 is connected to the terminal 101 via a network, and can be used to feed back the parallel insertion results of the insert select to the terminal. The database 105 can be set on the server or independently of the server to provide data storage services for the server 103. The above-mentioned network includes but is not limited to: a wide area network, a metropolitan area network or a local area network, and the terminal 101 includes but is not limited to a PC, a mobile phone, a tablet computer, etc.

[0048] A parallel insertion method of insert select within a database transaction in an embodiment of the present application can be executed by the server 103.

[0049] An embodiment of the present application provides a parallel insertion method of insert select within a database transaction, which can be applied to a server to improve the performance of importing data into a database.

[0050] The following will be combined with a specific implementation method to provide a parallel insertion method of insertselect in a database transaction provided by an embodiment of the present application in detail. Figure 2 As shown, the specific steps are as follows:

[0051] Step 201: when receiving an insert select request sent by a terminal, splitting the data in the source table into different data intervals through the main thread of the database, wherein the insert select request is used to copy the data in the source table to the target table.

[0052] Step 202: Create sub-threads having the same number as the number of data intervals through the main thread, wherein the main thread also serves as a sub-thread.

[0053] Step 203: Allocate the data in each data interval to a sub-thread, and control the multiple sub-threads to concurrently perform insert operations on the target table, wherein each sub-thread is used to serially insert the data in the corresponding data interval into the target table.

[0054] In this application, a database is a software system for storing and managing data. In this system, various data operations such as insert, update, delete, etc. can be performed; a source table is a table containing original data, which will be imported into a target table. Exemplarily, the source table is the t1 table; a target table is a table used to receive data imported from the source table. Exemplarily, the target table is the t2 table. The database is an overall management system, and the source table t1 and the target table t2 are two specific data tables. Through the SQL statement insert into t2 select*from t1, the data in the source table t1 can be imported into the target table t2. This method can improve the database import performance by splitting the sub-threads for concurrent insertion and using the main thread to coordinate and control the sub-threads to submit synchronously. The specific process is as follows.

[0055] The terminal sends an insert select request to the database. The insert select request is used to request the database to copy the data in the source table to the target table to achieve data migration and synchronization. The user node thread of the database receives the insert select request sent by the client. This thread serves as the coordinator of the entire parallel insert operation, that is, the main thread of the insert select parallel operation.

[0056] The main thread determines the degree of parallelism for parallel data insertion based on the amount of data in the source table and the system resources of the database, and evenly splits the source table data into corresponding data intervals based on the degree of parallelism. There are two specific splitting methods, which will be explained in detail below and will not be repeated here.

[0057] The main thread creates or wakes up a corresponding number of child threads according to the number of data intervals, where the child threads independently perform the data insertion work of the transaction under the control of the main thread. In addition to executing the work of the child threads, the main thread is also responsible for coordinating and managing the execution process of the entire transaction.

[0058] The main thread assigns each split data interval to a child thread, and multiple child threads concurrently perform insert operations on the target table. Specifically, the main thread splits the data in the table into multiple data intervals, each data interval includes multiple data, and multiple child threads simultaneously perform insert operations on multiple data intervals, so that the data in multiple data intervals are inserted into the target table in parallel. When each child thread performs an insert operation on multiple data in the corresponding data interval, it still inserts the data one by one in a serial manner.

[0059] In this application, the source table data is split into multiple data intervals, and concurrent insertion operations are performed on the data in the multiple data intervals through multiple sub-threads to achieve concurrent insertion of data in the source table into the target table. Compared with the serial insertion in the prior art, the parallel insertion in this application improves the insert select efficiency in the database and improves the data import performance in the database.

[0060] There are two implementation methods for splitting the data in the source table into different data intervals through the main thread of the database.

[0061] In the first implementation, data partitioning is performed directly. If a database histogram is not created in the source table, all data nodes in the distributed database are queried; the total data volume of all data nodes is counted; the total data volume is divided into equal parts according to the degree of parallelism, and each segment of the divided data is inserted into a data interval.

[0062] For source tables without histograms or where it is impossible to split data relatively evenly based on a certain field, direct splitting can be used. Specifically, a distributed database includes multiple data nodes, and the main thread queries the multiple data nodes. During the query process, the total data volume of the multiple data nodes is counted, which includes the total number of rows in the table and the average number of bytes per row.

[0063] In addition, the degree of parallelism for data insertion needs to be calculated. The degree of parallelism can be manually entered by the user or calculated by the main thread. If it is calculated, the main thread first needs to understand the total amount of data in the source table. By calculating the total amount of data, the time and resources required for the import operation can be estimated, and then the database system resources can be calculated. The database system resources include CPU resources, memory resources, and I / O resources.

[0064] CPU resources: used to evaluate the available CPU resources in the current system. Parallel processing requires a large amount of CPU resources for data processing and logging. It is necessary to ensure that there are enough CPU resources in the system to support parallel processing operations.

[0065] Memory resources: used to evaluate the available memory resources in the current system. During parallel processing, data needs to be cached in memory to increase processing speed. It is necessary to ensure that there are enough memory resources in the system to support parallel processing operations.

[0066] I / O resources: used to evaluate the available I / O resources in the current system. A large number of read and write operations are required during parallel processing, so it is necessary to ensure that there are enough I / O resources in the system to support these operations.

[0067] Exemplarily, the degree of parallelism may be calculated using the following formula.

[0068] Degree of parallelism = data volume / (batch size*number of CPU cores), where batch size refers to the amount of data processed by each subthread at a time, and number of CPU cores refers to the number of CPU cores available in the system.

[0069] The main thread splits the total data volume into equal parts according to the degree of parallelism. Each segment of the split data belongs to a data interval, thus obtaining multiple data intervals. The number of data intervals is the same as the number of parallelism. Subsequently, multiple sub-threads need to be created according to the number of parallelism. Multiple sub-threads can be created in batches for all data intervals, or one sub-thread can be created for each data interval.

[0070] Assume that there is an existing distributed table T1 with a total data volume of m. No histogram is created for table T1. If you want to insert the data of table T1 into table T2 with a parallelism of p, first perform a full table query on table T1. Each time N / P pieces of data are queried, they are divided into a data interval. This will result in N / P data intervals. Then, P threads are started in batches, and each thread inserts N / P pieces of data.

[0071] This method realizes parallel insertion of data during execution, which is faster than the single-threaded serial method and improves database performance.

[0072] In the second implementation, a histogram is used for data splitting. Specifically, the amount of data in the source table and the system resources of the database are obtained through the main thread of the database; the degree of parallelism of parallel data insertion is determined according to the amount of data and the system resources; the data distribution in the source table is analyzed using a database histogram to obtain multiple initial data intervals; if the difference in quantity between the degree of parallelism and the initial data interval is less than the set difference, the initial data interval is adjusted according to the degree of parallelism to obtain the final data interval.

[0073] In this application, the degree of parallelism calculated by the main thread is only a theoretical value, but it does not necessarily conform to the data distribution in the source table, so this application also uses a histogram for further verification. Specifically, the main thread can generate a histogram through an SQL statement. The histogram usually displays information such as the frequency, cumulative frequency, and frequency of the data. By viewing the results of the histogram, you can intuitively understand the distribution of the data and divide the data distribution into multiple data intervals. The size and number of data intervals can usually be determined based on the data field, frequency, and cumulative frequency. For example, the data can be divided into three intervals: high, medium, and low, or the data can be further subdivided into more data intervals according to actual needs.

[0074] The main thread compares the number of parallelism and the initial data interval. If the number is similar, that is, the difference between the parallelism and the initial data interval is less than the set difference, then the initial data interval is adjusted according to the parallelism to obtain the final data interval. For example, if the histogram is split into 4 data intervals and the parallelism is 8, then each data interval can be split into two data intervals, thus obtaining 8 data intervals.

[0075] If the number is very different, that is, the difference between the parallelism and the initial data interval is greater than or equal to the set difference, the initial data interval will be used as the final data interval according to the actual situation. For example, if the histogram is split into 2 data intervals and the parallelism is 1000, then the parallelism is no longer considered.

[0076] In the present application, when determining the final number of data intervals, both the amount of data and the system resources of the database are taken into consideration, so that the system resources of each concurrent line can carry the amount of data, and the actual distribution of the data is taken into consideration, in accordance with the actual data situation. In this way, the number of sub-threads for parallel data insertion can be reasonably determined, thereby maximizing the use of system resources while ensuring data processing efficiency.

[0077] As an optional implementation, in the process of using a database histogram to analyze the data distribution in the source table to obtain multiple initial data intervals, the method also includes: detecting in real time whether data operations occur in the source table; if data operations are detected, re-using the database histogram to analyze the adjusted data distribution to obtain adjusted data intervals.

[0078] The main thread detects in real time whether data insertion, deletion, update, and other operations occur in the source table. This monitoring mechanism can detect data changes in a timely manner, so that corresponding measures can be taken to maintain data consistency and integrity. When data changes are detected in the source table, it is necessary to determine whether these changes have led to changes in data distribution. Changes in data distribution may affect data management and query performance. Therefore, it is necessary to take timely measures to adjust data distribution. Based on the monitoring results, SQL statements can be used to generate histograms and analyze data distribution to obtain updated data intervals.

[0079] This application uses real-time monitoring and histogram analysis to deal with data changes and distribution adjustment issues in the database, which helps to better understand and manage data, so that more targeted measures can be taken in practical applications to optimize data processing and query performance.

[0080] For example, table T1 has a histogram with 4 intervals, [0, K1), [K1, K2), [K2, K3), [K4,”∞), the total data volume of table T1 is N, and the data volume of the 4 intervals remains the same N / 4±1. After insert, update, and delete operations are performed on table T1, the total data volume of table T1 becomes N1, and the intervals are automatically adjusted to [0, K5), [K5, K6), [K6, K7), [K7,”∞), and the data volume of the 4 intervals remains the same N1 / 4+1.

[0081] As an optional implementation, controlling multiple sub-threads to concurrently perform insert operations on a target table includes: controlling multiple sub-threads to concurrently insert data in a corresponding data interval in a source table into the target table, wherein the multiple sub-threads are in one transaction and the data insertion operation can be revoked; controlling multiple sub-threads to submit data, wherein the data submission is used to permanently save the data in the source table into the target table.

[0082] The concurrent execution of insert operations on the target table by multiple child threads includes two steps: data insertion and data submission.

[0083] 1. Data insertion:

[0084] Data insertion is the operation of adding new data rows to a table, usually implemented using the INSERT INTO statement. In a transaction, data insertion is the process of copying data from a source table to a target table, which includes copying data rows from one table to another, but does not immediately save these data rows permanently to the target table. Data insertion operations are reversible, that is, if an error occurs during the insertion process or the transaction needs to be rolled back, these insertion operations can be undone.

[0085] 2. Data submission:

[0086] Data commit is the operation of permanently saving the inserted data rows to the target table, which is usually implemented using the COMMIT statement. In a transaction, data commit is to formally write the inserted data rows to the target table to ensure that the data persists in the database. This step is usually irreversible. Once committed, the changes to the data cannot be undone. The data commit operation marks the completion of a transaction and ensures that all changes made in the transaction are permanently saved to the database.

[0087] Data insertion is the process of adding new data rows to the target table, while data commit is the process of permanently saving the inserted data rows to the target table. These two operations have different functions and importance in database transaction management.

[0088] As an optional implementation, before controlling multiple sub-threads to submit data, the method also includes: if a query operation on the set data in the source table is detected, determining the state of the set transaction in the distributed database where the set data is located; if the set transaction is in an active state, prohibiting querying the data in the set transaction, wherein the active state is used to indicate that at least some of the sub-threads have failed to submit; if the set transaction is in an inactive state, allowing querying the data in the set transaction, wherein the inactive state is used to indicate that all sub-threads have submitted successfully.

[0089] The database in this application adopts a distributed form, and the distributed database has availability, consistency and scalability.

[0090] Availability: When the data within a transaction is distributed across multiple data nodes, it means that the data rows involved are stored on multiple different database servers or instances. This architecture improves the availability and performance of the system because even if a node fails, other nodes can still provide services. In addition, distributed databases also support parallel processing and load balancing of data, thereby improving the scalability and flexibility of the system.

[0091] Consistency: To ensure data consistency, distributed database systems usually need to use some consistency protocols to coordinate operations between nodes. For example, the two-phase commit protocol (2PC) is used to ensure transaction consistency across multiple nodes. In addition, distributed databases may also involve complex replication and synchronization mechanisms to ensure that data on different nodes remains consistent.

[0092] Scalability: Distributed databases have good scalability. The processing power and storage capacity of the system can be improved by adding more nodes. Each node can independently expand its hardware resources and computing power, thereby improving the overall performance and throughput of the system. In addition, distributed databases can also distribute query requests to different nodes through a load balancing mechanism, further improving the response speed and stability of the system.

[0093] This application uses database transactions. A transaction is a logical unit in the execution process of a database management system. It consists of a finite sequence of database operations. The transaction is atomic, that is, the transaction is regarded as an indivisible minimum unit. The operations in the transaction are either all executed or not executed at all to ensure the integrity of the data. The multiple sub-threads in this application are located in a database transaction. For a distributed database, when the data in the same transaction is distributed on multiple data nodes, it means that the data rows involved in the source table are stored in multiple different database servers.

[0094] When multiple users access the database concurrently, such as when operating the same table, the transaction opened by the database for each user cannot be interfered by the operation of other transactions before submission is completed, and multiple concurrent transactions must be isolated from each other. From the perspective of optimal submission efficiency, the submission of the main thread and the child thread of the insert select operation needs to be performed at the same time. Since there is a sequence in which each thread completes the submission action, it is possible that the data submitted by some threads will be accessed by other transactions before the main thread responds to the terminal, resulting in the mixing of data between transactions. Therefore, before the insert select is submitted, if a query operation for the set data in the source table is detected, it is necessary to determine the set transaction to which the set data belongs in the distributed database, and determine whether to allow the source table data to be queried based on the status of the set transaction.

[0095] The global transaction manager assigns a unique global transaction ID to each transaction. All tables on the data node have a hidden column, which is a column that is not directly exposed to the outside. In parallel processing and transaction management, the hidden column is used to record status information related to the global transaction identifier ID. When a data row in the source table is inserted or updated, the hidden column of the row will be updated to the corresponding assigned global transaction ID. By checking the value of the hidden column, you can determine whether a data row is in an active global transaction.

[0096] Only when all the split sub-threads are successfully submitted, the corresponding transaction ID status in the global transaction manager will be set to inactive, and the data rows corresponding to the active transaction ID will be rejected for query. This ensures that in the scenario where some sub-threads are successfully submitted and some are unsuccessful, the abnormal data of some successful submissions cannot be queried.

[0097] For example, suppose there is an existing parallel statement insert into Tl select*fom T0. This statement is split into N parallel sub-threads for execution. The state of the globally unique transaction pre-allocated by the statement is G. Among them, sub-threads 1-K are successfully submitted, and sub-threads N-K+1 fail to submit. At this time, the hidden column in each row of data that is successfully submitted will be updated to G. Since some sub-threads fail to submit, the state of the globally unique transaction ID is active.

[0098] If there is a query statement to query the data of table T1, the status of the value G in the hidden column of the submitted data row will be determined before reading the data. In the active state, reading the data involved in the transaction is prohibited. This ensures that even if the parallel statement is split into multiple data nodes, the data still meets the requirements of overall visibility or overall invisibility.

[0099] On the contrary, if all N child threads are submitted successfully, then the hidden columns involved in the data are not G, and the state of the globally unique transaction ID is inactive. At this time, the data can be queried and read.

[0100] In the case where some sub-threads submit successfully and some sub-threads fail to submit, the present application prohibits all data involved in the transaction from being queried and read, ensuring that the data as a whole is invisible. In addition, when all sub-threads submit successfully, all data involved in the transaction is allowed to be queried and read, ensuring that the data as a whole is visible, thereby executing all sub-threads as a whole to achieve the atomicity of the transaction. In addition, if only some sub-threads submit successfully, the data as a whole is invisible, avoiding interference of other transactions on the current transaction, and achieving the isolation of the transaction.

[0101] Based on the same technical concept, the present application provides a parallel insertion flow chart of insert select within a database transaction, such as Figure 3 As shown, the following steps are included.

[0102] Step 1. The terminal sends an insert select request to the database.

[0103] Step 2. The main thread determines the degree of parallelism and determines the data interval according to the degree of parallelism. There are two ways to determine the data interval. One is to directly use the number of parallelism as the number of data intervals, and the other is to use a histogram to determine the initial data interval and then adjust the number of data intervals according to the degree of parallelism.

[0104] Step 3. Create child threads with the same number as the data interval.

[0105] Step 4. The child threads insert data in parallel.

[0106] Step 5. The child thread submits data.

[0107] Step 6. Send the insertion result to the terminal.

[0108] Based on the same technical concept, the present application provides a parallel insertion device for insert select in a database transaction, such as Figure 4 As shown, the device comprises:

[0109] The splitting module 401 is used to split the data in the source table into different data intervals through the main thread of the database when receiving the insert select request sent by the terminal, wherein the insert select request is used to copy the data in the source table to the target table;

[0110] A creation module 402 is used to create, through a main thread, a number of child threads that is the same as the number of data intervals, wherein the main thread also serves as a child thread;

[0111] The insert module 403 is used to assign the data in each data interval to a sub-thread, and control multiple sub-threads to concurrently perform insert operations on the target table, wherein each sub-thread is used to serially insert the data in the corresponding data interval into the target table.

[0112] Optionally, the device is also used for:

[0113] Obtain the amount of data in the source table and the system resources of the database through the main thread of the database;

[0114] Determine the degree of parallelism for parallel data insertion based on the amount of data and system resources;

[0115] Use database histogram to analyze the data distribution in the source table and obtain multiple initial data intervals;

[0116] If the difference between the degree of parallelism and the initial data interval is less than the set difference, the initial data interval is adjusted according to the degree of parallelism to obtain the final data interval.

[0117] Optionally, the device is also used for:

[0118] If the difference between the parallelism and the initial data interval is greater than or equal to the set difference, the initial data interval will be used as the final data interval.

[0119] Optionally, the device is also used for:

[0120] Real-time detection of whether data operations occur in the source table;

[0121] If data manipulation is detected, the adjusted data distribution is analyzed again using the database histogram to obtain the adjusted data interval.

[0122] Optionally, the database is a distributed database, and the splitting module 401 is used to:

[0123] If the database histogram is not created in the source table, query all data nodes in the distributed database;

[0124] Count the total data volume of all data nodes;

[0125] The total data volume is divided into equal parts according to the degree of parallelism, and each segment of the divided data is inserted into a data interval.

[0126] Optionally, the insertion module 403 is used to:

[0127] Control multiple sub-threads to concurrently insert data in the corresponding data interval in the source table into the target table, where the multiple sub-threads are in one transaction and the data insertion operation can be undone;

[0128] Control multiple child threads to submit data, where data submission is used to permanently save data in the source table to the target table.

[0129] Optionally, the device is also used for:

[0130] If a query operation on the setting data in the source table is detected, the state of the setting transaction in the distributed database where the setting data is located is determined;

[0131] If the transaction is set to an active state, querying the data in the transaction is prohibited, wherein the active state is used to indicate that at least some of the child threads have failed to submit;

[0132] If the transaction is set to an inactive state, the data in the transaction is allowed to be queried, wherein the inactive state is used to indicate that all sub-threads are successfully submitted.

[0133] like Figure 5 As shown, an embodiment of the present application provides an electronic device, including a processor 501, a communication interface 502, a memory 503 and a communication bus 504, wherein the processor 501, the communication interface 502, and the memory 503 communicate with each other through the communication bus 504.

[0134] The memory 503 is used to store computer programs.

[0135] In one embodiment of the present application, the processor 501 is used to implement the parallel insertion method of insert select in a database transaction provided by any one of the aforementioned method embodiments when executing the program stored in the memory 503, including:

[0136] When receiving the insert select request sent by the terminal, the data in the source table is split into different data intervals through the main thread of the database, wherein the insert select request is used to copy the data in the source table to the target table;

[0137] Create child threads with the same number as the number of data intervals through the main thread, wherein the main thread also serves as a child thread;

[0138] The data in each data interval is assigned to a sub-thread, and the multiple sub-threads are controlled to concurrently perform insert operations on the target table, wherein each sub-thread is used to serially insert the data in the corresponding data interval into the target table.

[0139] Optionally, before splitting the data in the source table into different data intervals through the main thread of the database, the method further includes:

[0140] Obtain the amount of data in the source table and the system resources of the database through the main thread of the database;

[0141] Determine the degree of parallelism for parallel data insertion based on the amount of data and system resources;

[0142] Use database histogram to analyze the data distribution in the source table and obtain multiple initial data intervals;

[0143] If the difference between the degree of parallelism and the initial data interval is less than the set difference, the initial data interval is adjusted according to the degree of parallelism to obtain the final data interval.

[0144] Optionally, after obtaining a plurality of initial data intervals, the method further includes:

[0145] If the difference between the parallelism and the initial data interval is greater than or equal to the set difference, the initial data interval will be used as the final data interval.

[0146] Optionally, in the process of using a database histogram to analyze the data distribution in the source table to obtain multiple initial data intervals, the method further includes:

[0147] Real-time detection of whether data operations occur in the source table;

[0148] If data manipulation is detected, the adjusted data distribution is analyzed again using the database histogram to obtain the adjusted data interval.

[0149] Optionally, the database is a distributed database, and splitting the data in the source table into different data intervals through the main thread of the database includes:

[0150] If the database histogram is not created in the source table, query all data nodes in the distributed database;

[0151] Count the total data volume of all data nodes;

[0152] The total data volume is divided into equal parts according to the degree of parallelism, and each segment of the divided data is inserted into a data interval.

[0153] Optionally, controlling multiple child threads to concurrently perform insert operations on the target table includes:

[0154] Control multiple sub-threads to concurrently insert data in the corresponding data interval in the source table into the target table, where the multiple sub-threads are in one transaction and the data insertion operation can be undone;

[0155] Control multiple child threads to submit data, where data submission is used to permanently save data in the source table to the target table.

[0156] Optionally, the database is a distributed database, and before controlling multiple sub-threads to submit data, the method further includes:

[0157] If a query operation on the setting data in the source table is detected, the state of the setting transaction in the distributed database where the setting data is located is determined;

[0158] If the transaction is set to an active state, querying the data in the transaction is prohibited, wherein the active state is used to indicate that at least some of the child threads have failed to submit;

[0159] If the transaction is set to an inactive state, the data in the transaction is allowed to be queried, wherein the inactive state is used to indicate that all sub-threads are successfully submitted.

[0160] An embodiment of the present application also provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of the parallel insertion method of insertselect within a database transaction provided by any of the aforementioned method embodiments are implemented.

[0161] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0162] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a general hardware platform, and of course, by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the relevant technology 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 ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0163] It should be understood that the terms used herein are only for the purpose of describing specific example embodiments and are not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms "one", "an" and "said" as used herein may also be meant to include plural forms. The terms "include", "comprise", "contain", and "have" are inclusive, and therefore specify the existence of stated features, steps, operations, elements and / or parts, but do not exclude the existence or addition of one or more other features, steps, operations, elements, parts, and / or combinations thereof. The method steps, processes, and operations described herein are not interpreted as necessarily requiring them to be performed in the specific order described or illustrated, unless the execution order is clearly indicated. It should also be understood that additional or alternative steps may be used.

[0164] The foregoing is merely a specific embodiment of the present invention, which enables those skilled in the art to understand or implement the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A parallel insertion method for insert select in a database, characterized in that: The method comprises: When receiving an insert select request sent by a terminal, splitting the data in the source table into different data intervals through the main thread of the database, wherein the insert select request is used to copy the data in the source table to the target table; Creating, by the main thread, child threads having the same number as the number of data intervals, wherein the main thread also serves as a child thread; The data in each of the data intervals is assigned to a sub-thread, and the multiple sub-threads are controlled to concurrently perform insert operations on the target table, wherein each sub-thread is used to serially insert the data in the corresponding data interval into the target table.

2. The method according to claim 1, characterized in that Before splitting the data in the source table into different data intervals through the main thread of the database, the method further includes: Acquire the amount of data in the source table and the system resources of the database through the main thread of the database; Determining the degree of parallelism of parallel data insertion according to the data volume and the system resources; Using a database histogram to analyze the data distribution in the source table to obtain multiple initial data intervals; If the quantity difference between the parallelism and the initial data interval is less than the set difference, the initial data interval is adjusted according to the parallelism to obtain a final data interval.

3. The method according to claim 2, characterized in that After obtaining a plurality of initial data intervals, the method further includes: If the difference between the degree of parallelism and the initial data interval is greater than or equal to a set difference, the initial data interval is used as the final data interval.

4. The method according to claim 2, characterized in that: In the process of using a database histogram to analyze the data distribution in the source table to obtain multiple initial data intervals, the method further includes: Detect in real time whether data operations occur in the source table; If data manipulation is detected, the adjusted data distribution is analyzed again using the database histogram to obtain an adjusted data interval.

5. The method according to claim 2, characterized in that: The database is a distributed database, and the data in the source table is split into different data intervals through the main thread of the database, including: If no database histogram is created in the source table, query all data nodes in the distributed database; Counting the total amount of data of all the data nodes; The total data volume is divided into equal parts according to the degree of parallelism, and each segment of the divided data is inserted into a data interval.

6. The method according to claim 1, characterized in that Controlling multiple sub-threads to concurrently perform insert operations on the target table includes: Controlling multiple sub-threads to concurrently insert data in corresponding data intervals in the source table into the target table, wherein the multiple sub-threads are in one transaction and the data insertion operation can be revoked; Controlling multiple sub-threads to submit data, wherein the data submission is used to permanently save the data in the source table to the target table.

7. The method according to claim 6, characterized in that The database is a distributed database. Before controlling multiple sub-threads to submit data, the method further includes: If a query operation on the setting data in the source table is detected, determining the state of the setting transaction in the distributed database where the setting data is located; If the set transaction is in an active state, querying the data in the set transaction is prohibited, wherein the active state is used to indicate that at least some of the sub-threads fail to submit; If the set transaction is in an inactive state, querying the data in the set transaction is allowed, wherein the inactive state is used to indicate that all sub-threads are submitted successfully.

8. A parallel insertion device for insert select in a database transaction, characterized in that: The device comprises: A splitting module, used for splitting the data in the source table into different data intervals through the main thread of the database when receiving an insert select request sent by the terminal, wherein the insert select request is used to copy the data in the source table to the target table; A creation module, used for creating, through the main thread, sub-threads having the same number as the number of data intervals, wherein the main thread also serves as a sub-thread; The insertion module is used to assign the data in each data interval to a sub-thread and control multiple sub-threads to concurrently perform insertion operations on the target table, wherein each sub-thread is used to serially insert the data in the corresponding data interval into the target table.

9. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, for implementing any of the methods described in claims 1-7 when executing a program stored in a memory.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images