About database Optimization 3--performance optimizations for inserting and updating large amounts of data in a database

In a real business scenario, we must have encountered the need for large quantities of data to be put into storage or updated. At this time we are in the implementation of this kind of insertion, or update will certainly encounter the problem of database efficiency, the first thing we can think of is to make things as consistent as possible, unified to commit transactions, so there will certainly be some efficiency hints.

　　Ok. After we have considered this, we can look at whether there is an index in the table, and if there is an index, we can do one of the following: first, when inserting large amounts of data, the index is abolished first, and then after the insert is completed, the index is re-established. I have done a simple experiment here, collated the results of the experiment, with a diagram of the following performance efficiency.

　　

The experiment is that we have a T table, the T table has about 29W of data, first we set up n index for T table, in going to insert the T table once the original data is 15 seconds , and the T table is not indexed when we insert time is about 2 seconds , But the indexing time is a little slower, but certainly not more than 15 seconds, so our efficiency improvements are obvious. (T-table builds so many indexes just for the sake of the experimental effect is obvious, is to make up the data quantity not so big)

This time we can summarize: The index is temporarily invalidated before inserting the data, after the insert is completed, we re-index, although after the insert is complete, then re-index. With this operation our high-volume data operations will be more efficient, but it should be noted that when rebuilding the index, if the table is used elsewhere, it is possible to cause the indexing to fail, so this operation is suitable for use when the system usage is relatively small, for example, when performing a timed task, Or when you need to update or insert large amounts of data late at night.

　　The index causes the insertion or update to be slow: because the index is needed to maintain the larger the amount of data that the index needs to maintain, and the cost of this maintenance operation is high, so the above operation can make a large number of additions or updates have a fly boost. (in fact, the experiment was based on a requirement that our company had a need to make large batches of inserts and updates at a time every day, but that was the case, but after the company had a DBA, the DBA felt that the operation was unnecessary, and no one knew what the operation was for, Let's get rid of this operation, in their private discussion I overheard that we have low level of development, the mechanism of the database does not understand, so only these useless operations, I am very dissatisfied, so have the above experiment)

About database Optimization 3--performance optimizations for inserting and updating large amounts of data in a database