MYSQL 8 基本操作之09 (定期optimize tabl

1. 创建测试表

#获取当前的数据目录：
root@127.0.0.1 : (none)【10:05:36】1 SQL->show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+

root@127.0.0.1 : (none)【09:56:39】6 SQL->use testdb;

root@127.0.0.1 : testdb【09:56:46】7 SQL->create table test_text(id int,doc text);

[root@XAG816 testdb]# pwd
/usr/local/mysql/data/testdb

[root@XAG816 testdb]# ll test*
-rw-r----- 1 mysql mysql 114688 Jul 14 21:56 test_text.ibd

2. 插入测试数据

root@127.0.0.1 : testdb【09:56:49】8 SQL-> insert into test_text values(1,repeat('test ',100));

root@127.0.0.1 : testdb【10:00:03】9 SQL-> insert into test_text values(2,repeat('test ',100));

root@127.0.0.1 : testdb【10:00:10】10 SQL-> insert into test_text values(3,repeat('test ',100));
...
root@127.0.0.1 : testdb【10:01:39】20 SQL-> insert into test_text select * from test_text;
Query OK, 1536 rows affected (0.05 sec)

[root@XAG816 testdb]# ll test*
-rw-r----- 1 mysql mysql 10485760 Jul 14 22:01 test_text.ib

[root@XAG816 testdb]# du -sh test*
10M test_text.ibd

3. 删除部分数据

#删除id=1的数据（占空间的 1/3）
root@127.0.0.1 : testdb【10:01:40】21 SQL->delete from test_text where id=1;
Query OK, 1024 rows affected (0.09 sec)

[root@XAG816 testdb]# du -sh test*
10M test_text.ibd

4. optimize table 碎片整理

#注意：在OPTIMIZE TABLE运行过程中，MySQL会锁定表
root@127.0.0.1 : testdb【10:06:54】22 SQL->optimize table test_text;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| testdb.test_text | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| testdb.test_text | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+

[root@XAG816 testdb]# du -sh test*
7.0M    test_text.ibd