Using the Optimize table command to defragment a table fragment practice

Operating environment: Ubuntu 14.10 MySQL 5.6.25

For tables that contain BLOBs or text fields, if you frequently modify or delete classes, you will need to perform regular optimize table commands to defragment them.

1.create table T1 (id varchar (), content text) Engine=myisam, default Charset=utf8;

Storage Engine uses MyISAM

2. Inserting data

INSERT into T1 VALUES (1, repeat (' Tony ', 100)); --repeat (' Tony ', 100), return Tony Repeat 100 times after the data

INSERT into T1 values (2, repeat (' Tony ', 100));

INSERT into T1 VALUES (3, repeat (' Tony ', 100));

A good way to quickly insert large amounts of data into a table, repeat the following statement

INSERT INTO T1 select * from T1; --From T1 query out all the data, and then insert the T1 table, the data multiplied

3. View File Size

root@ubuntu2:/var/lib/mysql/tsetest# Du-sh t1.*
12K t1.frm
310M t1. MyD
4.0K t1. Myi

4. Delete data and view file size

mysql> Delete from t1 where id = 2;
Query OK, 262144 rows affected (2.04 sec)

root@ubuntu2:/var/lib/mysql/tsetest# Du-sh t1.*
12K t1.frm
310M t1. MyD
4.0K t1. Myi

The physical size of the table file is not reduced after a large amount of data has been deleted.

5. Defragment the table using the Optimize table command

mysql> optimize table T1;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| Tsetest.t1 | Optimize | Status | OK |
+------------+----------+----------+----------+
1 row in Set (2.34 sec)

root@ubuntu2:/var/lib/mysql/tsetest# Du-sh t1.*
12K t1.frm
206M t1. MyD
4.0K t1. Myi

After you use the Optimize table command to defragment the table, the table file size is reduced by nearly one-third.