Does MySql Delete and update operations affect performance? mysql Delete and update Performance

Does MySql Delete and update operations affect performance? mysql Delete and update Performance

The cost of Delete and update operations is usually higher than that of insert operations. Therefore, a good design requires less database update and delete operations.

3.1 update operations

Database update operations bring about a series of "effects": update operations need to record logs (so that errors can be rolled back); Update variable length fields (such as varchar type) changes will occur in the physical storage of data (the movement of records). Updating index fields will lead to re-indexing; Updating primary keys will lead to data restructuring. All this will not only cause low efficiency of the update operation, but also reduce the query performance in the future due to the generation of disk fragments. To cope with this situation, there are two strategies: 1. Reduce the number of updates and write updates to multiple fields in the same statement; 2. Avoid updates. These two policies apply to different situations, and the following is an example of the two cases.

3.1.1 reduce the number of updates
There is a code cleansing process in the integration library, that is, assigning values to the self-encoding fields of business data through the connection code table. Code cleansing is a process of updating a business data table by associating a code table. Multiple code tables need to be connected to update multiple self-encoding fields. To complete this update, you can write two update statements: one is to write multiple SQL statements, and each statement updates one self-encoding field; another method is to write all updates in one statement. The statement for updating the bank code is as follows:

updateTBL_INCOME_TMP AsetBANKCODESELF = ( select SELFCODE from TBL_BANKINFO B where A.BANKCODE = B.BANKCODE )

The statement that uses an update statement to update multiple self-encoding fields is as follows:

Copy codeThe Code is as follows:
UpdateTBL_INCOME_TMP

Set code 1 Self-encoding = get self-encoding by associating code 1 table,
Code 2 self-encoding = get self-encoding through association code 2 table,
...,
Code n self-encoding = get self-encoding by associating the code n table

Use 20 million of the test data. The test results of the two methods are shown in the following table. The test results show that the performance of an update method is improved by 10 times, greatly improving the performance.

Handling process

Time consumed for multiple update Methods

Time consumed for one update Method

Code cleansing

0:29:48
0:02:59

3.1.2 avoid updates

The following is a common example. A company has a staff attendance system. In order to improve the query statistics performance, some tables containing redundant information are created based on the original system. Take the employee table as an example. The process of obtaining data is 12. The first step is to place the employee information in the new table, and then connect to and update the Department name through the "department ID" field ".

Figure 12. Associate update

Generally, to save storage costs, design fields such as department names to be variable-length. Therefore, when updating it, disk data will be reorganized to form disk fragments, affecting query performance.

To avoid this situation, we can use the method shown in Figure 13 to avoid updates. This method completes the insertion of redundant data tables step by step, and then connects to the Department table to obtain the "department name" when inserting, thus avoiding the update operation.

Figure 13. Avoid updating

3.2 delete operation

Beginners may think that the delete operation is simple and can be completed quickly. In fact, this is an incorrect understanding. The deletion process requires a large number of disk scans; database logs need to be recorded; in addition, the deletion process does not release disk space, which wastes disks and disconnects data on disks, this is a fatal blow to the performance of subsequent queries. Two methods are usually used: 1. reorg the tables that frequently perform the delete operation; 2. Avoid deletion.

3.2.1 Reorganization

The reorg operation resorts the physical order of table data and removes the free space in the fragment data.

Because the delete operation does not release disk space, after the delete operation is performed, the table will become slice, which leads to a serious reduction in performance. This can also happen after multiple update operations. If statistics are collected but no obvious performance improvement is displayed, reorganizing the table data may be helpful. When reorganizing table data, reschedule the physical order of the Data Based on the specified index and remove the free space in the fragment data. This allows the data to be accessed more quickly, thus improving the performance.

3.2.2Avoid deleting-intermediate table and formal table Modes

The intermediate table and formal table modes are often used when data needs to be processed in a complex manner. The data is processed in the intermediate table, and the data that meets the conditions is transferred to the formal table. The data that does not meet the conditions is retained in the intermediate table. Figure 14 illustrates the process of transferring data from an intermediate table to a formal table: After data processing is completed, insert the data with flag = 1 in temp1 to the formal table, and delete the data with flag = 1 in the middle table temp1.

Figure 14. transfer data from an intermediate table to a formal table

Because the flag field is not a clustered index, when the intermediate table temp1 is deleted, a large number of fragments are left on the disk, as shown in Figure 15. Not only will there be so many disk fragments, but the deleted data space will not be automatically released. The result is not only a waste of disk space, but also a sharp drop in query performance.

Figure 15. Disk fragments after deletion

We can use the clear table command to avoid deletion. In addition to the intermediate table temp1 and the formal table, add the auxiliary temporary table temp2. If the flags of data retained in temp1 = 0 only occupy 10%, this optimization will significantly improve the performance. The procedure is as follows:

1. Insert the data with flag = 0 in temp1 to temp2.

2. Clear table temp1

Copy codeThe Code is as follows:
Alter table temp1 activate not logged initially with empty table;

3. insert data in temp2 into temp1

3.3 How to make access more efficient

A large part of this section is from The Art of SQL, which combines The general experience of database development. Although it is not limited to specific DBMS and hardware platforms, it is a very practical book.

1. Connect to the database at a time and do a lot of things. The connection is disconnected until the processing is complete.
2. An SQL statement contains as many operations as possible. In the image, thousands of statements are cyclically slow with the help of cursors. It is slow to process the same data with several statements. Replace it with a statement to solve the problem.
3. Close to the DBMS core. Try to use the built-in functions of the database. Reduce user-defined functions. Because the smart database optimizer does not know user-defined functions.
4. Do not join too many tables in a statement. We recommend that you set a maximum of five tables.
5. Merge frequently updated columns: When a row is updated, DB2 records all columns that have been changed. Therefore, putting frequently updated columns together can reduce DB2 record work. This is only a small recommendation on performance. Therefore, major application or database design modifications should not be made to implement it.

The above is all the content about the impact of MySql deletion and update on performance.