A large amount of data deletion is bound to cause discontinuous blank space in the data file, and when the data is inserted, the blank space is used.
Different storage engines are not the same way of defragmenting.
MyISAM
The following methods can be used:
mysql> Show Table status from test like ' testusers ' \g
1. Row ***************************
....
Rows:3
Avg_row_length:45
Data_free:40
.....
Because in the middle delete, 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, the Data_free has changed to 0. The fragmentation data is cleared.
You can also use the following methods, the same effect as optimize
./bin/mysqlcheck-uroot-proot--socket=./tmp/mysql.sock-o Test TestUsers
InnoDB
For InnoDB use optimize and Mysqlcheck are not working, you can do the following
For small tables, you can use ALTER TABLE TABLE_NAME to reclaim the table space, which cannot be used directly for large tables because it can cause a long locking table. You can transfer data using a new table, and then delete the old table and rename the table.
Another Python can view the InnoDB table space information, which can be found on the Internet, written in Python.