Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the reason why the disk space is still occupied after the Delete mysql table data". In the daily operation, I believe that many people have doubts about the reason why the disk space is still occupied after the Delete mysql table data. Xiaobian consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubt that "the disk space after the Delete mysql table data is still occupied". Next, please follow the editor to study!

Recently, there is a project in which the host computer acquires the data reported by the lower computer. Due to the frequent reporting frequency and large amount of data, the data grows too fast and the disk occupies too much.

To save money, data backups are performed periodically and table records are deleted through delete.

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

Mysql is used as the database in the project. For tables, it is generally 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, we do delete the data records in the table, but there is no change in the size of the table file.

Mysql data structure

Anyone who has used mysql must have heard of the B+ tree. MySQL InnoDB uses the B+ tree as the structure to store data, that is, the index organizes the table, and the data is stored according to the page. So when you delete data, there are two situations:

Delete some records in the data page

Delete the contents of the entire data page

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

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

InnoDB directly marks the record R4 as deleted, which is called a reusable location. If a record with an ID between 300and 700is later inserted, the location is reused. Thus, the size of the disk file will not be reduced.

General deletion of the whole page of data will also delete the record mark, and the data will be reused. Unlike deleting the dictation record, the whole page record can be deleted. When the inserted data is not in the original range, the location can be reused. If you only delete the dictation record, it can only be reused when the inserted data meets the location of the deleted record.

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

Then how can we make the watch smaller?

DELETE only deletes the data identity bits and does not collate the data files. When new data is inserted, the record space set to delete identification is used again. OPTIMIZE TABLE can be used to reclaim the unused space and defragment the data files.

OPTIMIZE TABLE table name

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

Alternatively, you can perform rebuilding the table through ALTER TABLE

ALTER TABLE table name ENGINE=INNODB

Some people may ask, what's the difference between OPTIMIZE TABLE and ALTER TABLE?

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

Online DDL

Finally, say again that one of Online DDL,dba 's daily work must be ddl changes, ddl changes will lock the table, which can be said to be a permanent pain in dba's heart, especially when implementing ddl changes, resulting in a large number of threads on the library in the "Waiting for meta data lock" state. So Online DDL was introduced after version 5. 6.

Before the introduction of Online DDL, there were two main ways to execute ddl: copy mode and inplace mode, inplace mode is also known as (fast index creation). Compared to copy mode, inplace mode does not copy data, so it is faster. However, this method only supports adding and deleting indexes, and it needs to lock the table all the way like copy, so it is not very practical. Compared with the first two methods, Online can not only read, but also support write operations.

When executing the online DDL statement, use the ALGORITHM and LOCK keywords, which are at the end of our DDL statement, separated by commas. Examples are as follows:

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

ALGORITHM option

INPLACE: replace: perform the DDL operation directly on the original table.

COPY: replication: using a temporary table method, clone a temporary table, execute DDL on the temporary table, and then import the data into the temporary table, renaming, etc. During this period, twice as much disk space is needed to support such an operation. During execution, the table does not allow DML operations.

DEFAULT: by default, MySQL chooses to use INPLACE first.

LOCK option

SHARE: shared lock. Tables that execute DDL can be read, but not written.

NONE: without any restrictions, the table that executes DDL is readable and writable.

EXCLUSIVE: exclusive lock, the table that executes DDL cannot be read or written.

DEFAULT: the default value, which is used when the LOCK clause is not specified in the DDL statement. If the value of LOCK is specified as DEFAULT, it is given to the MySQL clause to feel whether the table is locked or unlocked. Not recommended, if you are sure that your DDL statement will not lock the table, you can not specify lock or specify its value to default, otherwise it is recommended to specify its lock type.

When performing the DDL operation, the ALGORITHM option can be unspecified, and 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 not supported, an error will be reported directly.

Both OPTIMIZE TABLE and ALTER TABLE table name ENGINE=INNODB support Oline DDL, but it is still recommended when business visits are low.

Summary

When delete deletes data, the corresponding data row is not really deleted, it is just marked as reusable, so the tablespace does not become smaller.

You can rebuild the table by quickly reducing the size of the table after delete data (OPTIMIZE TABLE or ALTER TABLE). After version 5.6, the creation of the table already supports the operation of Online, but it is best to use it at a low business peak.

At this point, the study on "the reason why disk space is still occupied after Delete mysql table data" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.