Parse the cause of fragmentation in the mysql table and clear it. A large amount of data deletion will inevitably lead to discontinuous blank spaces in the data file. when inserting data, these blank spaces will be used. If a storage engine breaks down a large amount of data, it will inevitably lead to discontinuous blank space in the data file. when data is inserted, these blank spaces will be used.
Different storage engines are organized in different ways.
Myisam
You can use the following methods:
Mysql> show table status from test like 'testusers' \ G
* *************************** 1. row ***************************
....
Rows: 3
Avg_row_length: 45
Data_free: 40
.....
Because it is deleted in the middle, it is left 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 Shard data is cleared.
You can also use the following method, with the same effect as optimize.
./Bin/mysqlcheck-uroot-proot -- socket =./tmp/mysql. sock-o test testusers
Innodb
For innodb, optimize and mysqlcheck do not work. you can do the following:
For small tables, use alter table table_name; to recycle tablespaces. this method cannot be used directly for large tables because it will cause a long lock TABLE. You can create a new table to transfer data, delete the old table, and rename the table.
In addition, there is a python that can view innodb tablespace information. you can find it online and write it in python.
. Different storage engines are broken down...
