delete operation in MySQL

In InnoDB, the delete operation does not really delete data. In fact, mysql only marks the data to be deleted as deleted. The space occupied by the disk will not become smaller, that is, the table space is not really released.

Thinking of such design

1. mysql of delete The operation is only logically marked for deletion, and the data on the disk has not been really deleted.
2. This design is because if many other records need to be rearranged on the disk after being removed on the disk, it will consume a lot of performance.(For example, if an index exists in a large table and one row is deleted, the entire index structure will change, and the subsequent change of index structure will bring disk problems IO)
3. All deleted records will form a garbage linked list. The space occupied by the linked list records is called reusable space. The newly inserted record can overwrite this space.

1, Reuse table space

Lines marked for deletion can be reused. Next time there are qualified records, they can be directly inserted into the marked position.

1. Line record reuse

Like in ID 300-600 Delete between ID=500 The record is marked for deletion. Next time if any ID=400 You can reuse this record to insert ID=500 The location marked for deletion.

2. Data page reuse

This means that the entire data page is marked for deletion, so the entire data page can be reused. Unlike row record multiplexing, data page multiplexing has almost no restrictions on the data to be inserted.

2, Data hole

These records marked for deletion are data holes. It not only wastes space, but also affects the query efficiency.

1. mysql The bottom layer stores and reads data in the unit of data pages. Each time you read data to the disk is to read a data page, and each time you access a data page, it corresponds to one time IO Operation, disk IO The access speed is very slow.
2. If there are a large number of data holes in a table, the data originally saved in only one data page takes up space due to many holes. Other data pages have to be added to save the data mysql When querying the same data, you have to add disks IO Operation, which affects the query speed.
3. Not only deletion will cause data holes, but also insertion and update will cause data holes. Therefore, after a large number of frequent additions and deletions, a table will inevitably produce data holes and affect the query efficiency. In the production environment, the tables that originally query quickly become slower and slower.

3, Rebuild table

The principle of this command is to rebuild the table. Create A temporary table B, then query the data in A (table with data holes) and reinsert them into temporary table B. Finally, replace table A with temporary table B, which is the process of rebuilding the table.

optimize table <table_name>;

1. Size of space occupied by query table

use information_schema;

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='repeater' AND table_name='t_record';

2. alter rebuild table command

This command is recommended for rebuilding tables.

alter table <table_name> engine=InnoDB

3. The difference between alter and optimize

alter table t engine=InnoDB (that is, recreate), and optimize table t (that is, recreate + analyze)

4, Online DDL

ddl is the daily work of dba, and the table will be locked when ddl is executed. In particular, when ddl changes are executed, a large number of threads in the library are in the state of Waiting form meta data lock. Therefore, Online DDL was introduced after MySQL version 5.5

1. Before the launch of online DDL

Online DDL Before launch, execute ddl There are two main ways copy Mode and inplace Way, inplace The method is also called(fast index creation). 

be relative to copy Way, inplace Mode does not copy data, so it is faster. However, this method only supports adding and deleting indexes, and is different from copy In the same way, it needs to lock the table in the whole process, which is not very practical. Online Compared with the first two methods, this method can not only read, but also support write operations.

2. ALGORITHM and LOCK use

When executing an Online DDL statement, use the ALGORITHM and LOCK keywords, place them at the end of the DDL statement and separate them with commas

ALTER TABLE t_name ADD COLUMN username varchar(200) , ALGORITHM=INPLACE,LOKC=NONE;

3. ALGORITHM and LOCK

• ALGORITHM option
  • Replace: directly perform DDL operations on the original table
  • COPY copy: use a temporary table method to clone a temporary table, execute DDL on the temporary table, and then import the data into the temporary table and duplicate the name. During this period, twice as much disk space is required to support this operation. DML operation is not allowed for the table during execution
  • DEFAULT mode: selected by MYSQL itself. Use the replace mode first
• LOCK option
  • SHARE shared lock: the table executing DDL is readable but not writable
  • NONE has no restrictions: the table executing DDL is readable and writable
  • EXCLUSIVE exclusive lock: the table executing DDL is unreadable and writable
  • DEFAULT: the DEFAULT value used when the LOCK clause is not specified when DDL is executed. If the specified value is DEFAULT, it is left to the MYSQL clause to decide whether to LOCK the table or not. It is not recommended. If you are sure that the DDL statement will not LOCK the table, you can not specify LOCK or specify its value as DEFAULT. Otherwise, it is recommended to specify other types.

implement DDL During operation, ALGORITHM Options can not be specified. At this time MySQL according to INSTANT,INPLACE,COPY Automatically select the appropriate mode in the order of. You can also specify ALGORITHM=DEFAULT，The same effect. If specified ALGORITHM If this option is not supported, an error will be reported directly.

OPTIMIZE TABLE and ALTER TABLE Table name ENGINE=INNODB All support Oline DDL，However, it is still recommended to use it when the traffic is low.

5, Reference documents

mysql deletion is actually a false deletion

After MYSQL table data is delete d, why does it still occupy storage space?