Why is the disk space still occupied after deleting table data in MySQL?

Recently, there was a project in which the upper computer obtained data reported by the lower computer. Due to the frequent reporting frequency and large amount of data, the data grew too fast and the disk occupied a lot.

To save costs, back up data regularly and delete table records using delete command.

delete has been executed, but the size of the table file has not decreased, which is puzzling.

Mysql is used as the database in the project. For tables, it is generally the table structure and table data. The space occupied by the table structure is relatively small, and it is generally the space occupied by the table data.

When we use delete to delete data, the data records in the table are indeed deleted, but the table file size does not change.

1. Mysql data structure

Anyone who has used mysql must have heard of B+樹. MySQL InnoDB uses B+ tree as the structure for storing data, which is often called index organized table, and the data is stored by page. Therefore, when deleting data, there are two situations:

• Delete some records in the data page
• Delete the contents of the entire data page

2. The table file size has not changed and is related to MySQL design

For example, if you want to delete the record R4:

InnoDB directly marks the record R4 as deleted, which is called a reusable location. If you insert a record with ID between 300 and 700 later, this position will be reused.

It can be seen that the size of the disk file will not be reduced.

Generally deleting a whole page of data will also delete the record mark, and the data will be reused at that position. This is different from deleting dictation records in that when deleting a whole page of records, the position can be reused when the data inserted later is not within the original range. However, if you only delete dictation records, you need to insert data that matches the deleted record position before you can reuse it.

Therefore, whether a data row or a data page is deleted, it is marked as deleted for reuse, so the file size will not be reduced.

3. How can we make the table size smaller?

DELETE only deletes the data marker and does not defragment the data file. When new data is inserted, the record space marked as deleted will be used again. OPTIMIZE TABLE can be used to reclaim unused space and defragment the data file.

OPTIMIZE TABLE table name;

Note: OPTIMIZE TABLE works only for MyISAM , BDB , and InnoDB tables.

Alternatively, you can also rebuild the table using ALTER TABLE .

ALTER TABLE table name ENGINE=INNODB

Some people may ask what is the difference between OPTIMIZE TABLE and ALTER TABLE ?

alter table t engine = InnoDB (that is, recreate), while optimize table t is equal to recreate+analyze

4. Online DDL

Finally, let's talk about Online DDL . One of the daily tasks of dba is definitely DDL changes. DDL changes will lock the table, which can be said to be an eternal pain in dba heart, especially when executing ddl changes, causing a large number of threads on the library to be in the " Waiting for meta data lock " state. Therefore, Online DDL was introduced after version 5.6.

Before the launch of Online DDL , there were two main ways to execute DDL: copy and inplace . inplace method is also called ( fast index creation ). Compared with the copy method, inplace method does not copy data and is therefore faster. However, this method only supports adding and deleting indexes, and like the copy method, it requires locking the table throughout the process, so it is not very practical. Compared with the previous two methods, Online method supports not only reading but also writing operations.

When executing online DDL statements, use the ALGORITHM and LOCK keywords. These two keywords are at the end of our DDL statement, separated by a comma. Here is an example:

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM Options

• INPLACE: Replace: Execute the DDL operation directly on the original table.
• COPY: Copy: Use a temporary table to clone a temporary table, execute DDL on the temporary table, and then import the data into the temporary table, rename it, etc. During this period, twice as much disk space is required to support such operations. During execution, DML operations are not allowed on the table.
• DEFAULT: The default method, which is selected by MySQL itself, and the INPLACE method is used first.
  
• LOCK Option
• SHARE: Shared lock. The table where DDL is executed can be read but not written.
• NONE: There are no restrictions. The table where DDL is executed is readable and writable.
• EXCLUSIVE: Exclusive lock. The table where DDL is executed cannot be read or written.
• DEFAULT: The default value, which is the default value used when LOCK clause is not specified in the DDL statement. If the specified LOCK value is

DEFAULT , that is, it is up to MySQL to decide whether to lock or not lock the table. It is not recommended. If you are sure that your DDL statement will not lock the table, you can not specify lock or specify its value default . Otherwise, it is recommended to specify its lock type.
When performing DDL operations, the ALGORITHM option does not need to be specified. In this case, MySQL automatically selects the appropriate mode in the order of INSTANT , INPLACE , and COPY . You can also specify ALGORITHM=DEFAULT , which has the same effect. If the ALGORITHM option is specified but is not supported, an error is reported.

OPTIMIZE TABLE and ALTER TABLE table name ENGINE=INNODB both support Oline DDL , but it is still recommended to use them when the business access volume is low.

5. Summary

When delete data, the corresponding data row is not actually deleted, but is simply marked as reusable, so the table space will not become smaller.

You can rebuild the table to quickly reduce the size of the table after delete data ( OPTIMIZE TABLE or ALTER TABLE ). After version 5.6, table creation already supports Online operations, but it is best to use it during low business peaks.

This is the end of this article about why disk space is still occupied after MySQL table data is deleted. For more information about MySQL table data deletion, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!