How to solve the problem that the mysql delete operation is actually a fake deletion

mysql deletion operation is actually a fake deletion

In InnoDB, your delete operation will not actually delete the data. Mysql actually just marks the deleted data as deleted. , so if you use delete to delete the data in the table, the space occupied by the table file on the disk will not become smaller. We will call it false deletion for the time being.

We can verify it through an example

Let’s follow the example in the previous article. First create a stored procedure, insert 100,000 pieces of data, and then see how much of the 100,000 pieces of data occupy space.

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

#定义分割符号，mysql 默认分割符为分号;，这里定义为 //
#分隔符的作用主要是告诉mysql遇到下一个 // 符号即执行上面这一整段sql语句
delimiter //

#创建一个存储过程，并命名为 testData
create procedure testData()

#下面这段就是表示循环往表里插入10w条数据
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end //  #这里遇到//符号，即执行上面一整段sql语句

delimiter ; #恢复mysql分隔符为;

call testData(); #调用存储过程

#下面这两条命令可以查看表文件所占空间大小
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 3.52M                                           |
+-------------------------------------------------+
1 row in set (0.04 sec)

You can see that 100,000 pieces of data occupy 3.52M of space in mysql. Then we execute the delete command delete from t and take a look.

#先删除表所有数据，再重新查看表文件大小
mysql> delete from t;
Query OK, 100000 rows affected (0.46 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 3.52M                                           |
+-------------------------------------------------+
1 row in set (0.00 sec)

It can be found from the results that after the table data is cleared, the space occupied by the table does not change. This verifies the above conclusion. The delete operation does not actually delete the data, and the space of the table is not released.

These deleted record rows are only marked for deletion and can be reused. Next time a record that meets the conditions can be directly inserted into this marked position.

For example, if we delete a record with id=500 among the records with id between 300-600, this record will be marked for deletion. If there is a record with id=400 to be inserted next time Come in, then you can reuse the location marked for deletion with id=500. This situation is called line record reuse.

Another situation is data page reuse, which means that the entire data page has been marked and deleted, so the entire data page can be reused, along with row records. The difference is that data page reuse has almost no restrictions on the data to be inserted.

Taking the above insertion as an example, if the record to be inserted is id=1000, then the position id=500 cannot be reused, but if there is an entire data page that can be reused, then regardless of the id Any value can be reused on this page.

These records that have been marked for deletion are actually a hole. They feel like they are occupying a manhole and not taking a shit. Not only is it a waste of space, but it will also affect the query efficiency.

Because you have to know that mysql stores and reads data in units of data pages at the bottom layer. Every time you read data from the disk, you read a data page. However, every time you access a data page, you need to read it once. Disk IO operations, disk IO is quite slow compared to memory access speed.

So think about it, if there are a large number of data holes in a table, the data that originally only needs one data page to save will have to be saved by adding other data pages due to the space occupied by many holes. Data, accordingly, when mysql queries the same data, it has to increase disk IO operations, thus affecting the query speed.

In fact, not only deletion operations will cause data holes, but insertions and updates will also cause holes. I won’t go into details here, just know it.

Therefore, after a data table undergoes a large number of frequent additions, deletions and modifications, it is inevitable that data holes will occur, wasting space and affecting query efficiency. Usually in a production environment, this will directly manifest itself as the originally fast query will become Slower and slower.

In this case, we can usually use the following command to solve the data hole problem.

optimize table t

The principle of this command is to rebuild the table, which is to create a temporary table B, then query all the data in table A (the table with data holes), and then reinsert all the data into temporary table B , and finally replace table A with temporary table B. This is the process of rebuilding the table.

Let’s try it again.

Look at the effect

mysql> optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.39 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 0.02M                                           |
+-------------------------------------------------+
1 row in set (0.00 sec)

You can see that the table file size has become 0.02M, indicating that the table space has been released. This 0.02M should be the size of the file that defines the table structure.

In addition, the following command can also be used to rebuild the table, which can achieve the same effect as above. It is recommended that you use the following command. You can try it.

alter table t engine=InnoDB

Note that the content of this article is based on the InnoDB engine, and there may be some differences for other engines.

The above is the detailed content of How to solve the problem that the mysql delete operation is actually a fake deletion. For more information, please follow other related articles on the PHP Chinese website!