A large amount of deleted data will inevitably result in discontinuous white space in the data file, and when the data is inserted, the white space will be exploited.
There are different ways to defragment the storage engine.
MyISAM
You can do this in the following ways:
Mysql> Show table status from test like ' testusers ' \g
1. Row ***************************
....
Rows:3
Avg_row_length:45
Data_free:40
.....
Because in the middle of the deletion, so left a blank
mysql> optimize table testusers;
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| Test.testusers | Optimize | Status | OK |
+----------------+----------+----------+----------+
1 row in Set (0.00 sec)
Mysql> Show table status from test like ' testusers ' \g
1. Row ***************************
...
Rows:3
Avg_row_length:32
data_length:96
data_free:0
1 row in Set (0.00 sec)
After optimize, Data_free has changed to 0. The fragment data is cleared.
The same can also be done in the following ways, the same effect as optimize
./bin/mysqlcheck-uroot-proot--socket=./tmp/mysql.sock-o Test TestUsers
InnoDB
For InnoDB use optimize and mysqlcheck do not work, you can proceed as follows
For small tables, use ALTER TABLE TABLE_NAME, and reclaim table space, which cannot be used directly for large tables, because it creates a long lock table. You can transfer data by using a new table, then delete the old table Form, and then rename the table.
There is also a Python can view the InnoDB table space information, can be found on the Internet Kazakhstan, written in Python.
http://www.bkjia.com/PHPjc/327756.html www.bkjia.com true http://www.bkjia.com/PHPjc/327756.html techarticle a large amount of deleted data will inevitably result in discontinuous white space in the data file, and when the data is inserted, the white space will be exploited. Defragment for different storage engines ...
