Home > Article > Backend Development > Analyzing the causes of fragmentation in mysql tables and cleaning them_PHP tutorial
Analyzing the causes of fragmentation in mysql tables and cleaning them_PHP tutorial
- WBOYOriginal
- 2016-07-21 15:04:331051browse
Deleting a large amount of data will inevitably cause discontinuous blank spaces in the data file, and when data is inserted, these blank spaces will be used.
Different storage engines defragment in different ways.
myisam
can have 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 was deleted in the middle, it left a blank space
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 become 0. Fragmented data is cleared.
You can also use the following method, the effect is the same as optimize
./bin/mysqlcheck -uroot -proot --socket=./tmp/mysql.sock -o test testusers
innodb
For innodb, neither optimize nor mysqlcheck will work. You can proceed as follows
For small tables, use ALTER TABLE table_name directly; recycle table space, for large tables This method cannot be used directly because it will cause a long-term table lock. You can transfer data by creating a new table, then deleting the old table, and then renaming the table.
There is also a python that can view innodb table space information. You can find it online and it is written in python.