Does MySQL delete and update operations have an impact on performance? _mssql

Deletion and update operations are often more expensive than inserts, so a good design requires less update and deletion of the database.

3.1 Update operation

The update operation of the database will bring a series of "Effects": Update operations require logging (to be rolled back when errors occur), and updating variable-length fields (such as varchar types) can result in changes in physical storage of data (the movement of Records); Updating index fields results in index rebuilding, and updating primary keys can result in data reorganization. This will not only cause the update operation itself inefficient, and due to the production of magnetic fragments will result in the subsequent query performance reduction. In order to deal with this situation, there are two strategies: first, reduce the number of updates, the update of multiple fields to the same statement, two, to avoid updates. These two strategies apply to different situations, and the following examples illustrate two scenarios.

3.1.1 Reduce the number of updates
There is a code cleaning process in the consolidation library that assigns a value to the Code field of the business data by connecting the Codes table. Code cleaning is actually a process of updating a business datasheet by associating a code table, which requires connecting multiple code tables and updating multiple self coded fields. To complete this update, there are two types of UPDATE statements: One is to write multiple SQL statements, one to update a self coded field, and the other to have all updates written in one statement. The UPDATE statement that updates the bank code looks like this:

Updatetbl_income_tmp A
setbankcodeself = (
 select Selfcode
 from
 tbl_bankinfo B
 where A.bankcode = B.bankcode)

A statement that implements more than one Code field update through an UPDATE statement is indicated as follows:

Copy Code code as follows:

Updatetbl_income_tmp

Set code 1 Self-encoding = encoded by the associated Code 1 table,
Code 2 self-encoding = encoded by the associated Code 2 table,
...，
Code n Self-coding = self-coding via associative Code N-Table

Use 20 million of the test data. The test results for both methods are shown in the following table. The test results show that the performance of one-Update method is increased by 10 times times and the performance is greatly improved.

Processing process

Multiple update methods time consuming

One-time Update method is time-consuming

Code Cleaning

0:29:48
0:02:59

3.1.2 Avoid updates

Here's a popular example of this kind of situation that is often encountered. A company has a system of staff attendance system, in order to improve the performance of query statistics, in the original system based on the establishment of a number of tables containing redundant information. Take the employee table as an example, the process for obtaining data is shown in Figure 12. The first step is to put the employee information in a new table, and then the connection updates the department name through the field Department ID connection.

Figure 12. Associating updates

In general, to save on storage expenses, the field is designed to be variable in length. Therefore, when it is updated, it will cause the disk data to be organized, resulting in disk fragmentation, affecting query performance.

To avoid this, we can use the method shown in Figure 13 to avoid the update. This method completes the insertion of the redundant data table in one step, and the Connection department table gets the "department name" when inserting, thus avoiding the update operation.

Figure 13. Avoid updates

3.2 Delete operation

Beginners may think that the deletion is simple and can be done quickly. In fact, this is a mistaken understanding that the removal process requires a large number of scan disks; The database log needs to be logged; and the removal process does not free up disk space, wastes disk, and makes the data on the disk fragmented, which is a fatal blow to the performance of subsequent queries. Usually in two ways to deal with: First, the frequent deletion of the table to reorganize (reorg), and second, to avoid deletion.

3.2.1 Reorganization

The reorganization (reorg) operation rearranges the physical order of the table data and removes the free space in the fragmented data.

Because the deletion does not free up disk space, the table becomes fragmented after the delete operation, which results in a significant performance degradation, which occurs after multiple update operations. If you collect statistics but do not see significant performance improvements, it may be helpful to reorganize the table data. When the table data is reorganized, the physical order of the data is rescheduled according to the specified index, and the free space in the fragmented data is removed. This allows the data to be accessed more quickly, thereby improving performance.

3.2.2 Avoid deletion--intermediate tables and formal table patterns

Intermediate tables and formal table patterns are often used when data requires more complex processing. The data is processed in an intermediate table, and then the data that satisfies the condition is transferred to the formal table, and the data that does not meet the condition remains in the middle table. Figure 14 illustrates the process of transferring data from an intermediate table to a formal table: After data processing, you need to insert the data in the middle table temp1 flag = 1 into the formal table and delete the data flag = 1 in the intermediate table Temp1.

Figure 14. Transferring 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 it leave so much disk fragmentation, but the space of the deleted data will not be automatically released. The result is a waste of disk space and a sharp drop in query performance.

Figure 15. Disk fragmentation after delete operation

We can use the Purge Table command to avoid deletion. In addition to the intermediate table Temp1 and the formal table, add the secondary temporary table Temp2. This optimization significantly improves performance if the data retained in Temp1 is only 10% flag=0. The specific steps are as follows:

1. Insert the flag=0 data in the Temp1 into the Temp2

2. Empty table Temp1

Copy Code code as follows:

ALTER TABLE TEMP1 ACTIVATE not logged initially with EMPTY table;

3. Insert the data in the Temp2 into the Temp1

3.3 How to make access more efficient

A large part of this section is derived from the book "The Art of SQL", which brings together the general experience of database development. Although not limited to specific DBMS and hardware platform, but it is a very practical book.

1. Connect the database once, do a lot of things. The connection will not be disconnected until it is finished processing.
2. An SQL statement contains as many operations as possible. Figuratively speaking: Thousands of statements, with the use of cursors constantly looping, very slow. It's slow to switch to a few statements to handle the same data. Change to a statement to solve the problem, the best.
3. Close to the DBMS core. Use a database-brought function as much as possible. Reduce the custom function. Because the Smarter database optimizer does not recognize custom functions.
4. One statement does not connect too many tables, the recommended upper limit is 5.
5. To centralize frequently updated columns: When a row is updated, DB2 records all the columns that made the changes, so putting the frequently updated columns together reduces the DB2 's record work. This is only a small recommendation about performance, so you should not make significant application or database design modifications to implement it.

The above is this article for MySQL delete and update on the performance impact of all the content, I hope to like.