Shulou(Shulou.com)06/03 Report--

This article is about how MySQL deletes table data but still takes up disk space. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. 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

2. The unchanged size of the table file is related to the 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.

3. How can we make the size of 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

4 、 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, that is to give the MySQL clause to feel whether to lock or not lock the table. 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.

Thank you for reading! About "MySQL delete table data but disk space has been occupied how to do" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.