Today I tested how to recycle a large number of deleted space in MySQL (version 5.5.27 ).
1. myisam table
Optimize table tablename ;#
Ii. innodb table (independent tablespace) -- innodb-file-per-table
A. Method 1: optimize table m;
Mysql> delete from m where id = 3;
Query OK, 262144 rows affected (11.12 sec) mysql> optimize table m;
+ ------- + ---------- + --------------------------------------------------------------------- +
| Table | Op | Msg_type | Msg_text |
+ ------- + ---------- + --------------------------------------------------------------------- +
| Stu. m | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| Stu. m | optimize | status | OK |
+ ------- + ---------- + --------------------------------------------------------------------- +
You can make optimize table work on other storage engines by starting mysqld with the -- skip-new or -- safe-mode option. in this case, optimize table is just mapped to alter table. mysql> optimize table m;
Query OK, 360448 rows affected (15.33 sec)
Records: 360448 Duplicates: 0 Warnings: 0
[Root @ localhost data] # ls-lh stu/
Total 30 M
-Rw ---- 1 mysql 8.4 K May 6 m. frm
-Rw ---- 1 mysql 29 M May 6 21:15 m. ibd
[Root @ localhost data] # ls-lh stu/
Total 21 M
-Rw ---- 1 mysql 8.4 K May 6 m. frm
-Rw ---- 1 mysql 20 M May 6 21:16 m. ibd
B. Method 2: Use alter table table_name engine = innodb # To delete the old table and create a new table.
[Root @ localhost data] # ls-lh stu/
Total 41 M
-Rw ---- 1 mysql 8.4 K May 6 m. frm
-Rw ---- 1 mysql 25 M May 6 21:04 m. ibd [root @ localhost data] # ls-lh stu/
Total 18 M
-Rw ---- 1 mysql 8.4 K May 6 m. frm
-Rw ---- 1 mysql 17 M May 6 21:04 m. ibd
Iii. innodb shared tablespace: MySQL 5.5 and earlier versions. I only used mysqldump to output table data and then import it to the new database (independent tablespace), which is equivalent to rebuilding the database. Please kindly advise if you have any good methods !!
