原理

optimize可以把分散（fragmented）存储的数据重新挪到一起(defragmentation)，清除碎片，回收闲置的数据库空间。

对于MyISAM表：

如果表已经删除或分解了行，则修复表
如果未对索引页进行分类，则进行分类
如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新

对于InnoDB表：
OPTIMIZE TABLE 被映射到 ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

使用

optimize table <table-name>;

例：

    MySQL [zuqiu]> optimize table sd_hongbao_diamond_detail_guidang;
    
    +--------------+----------+--------+----------------------------------------------------------------+
    | Table        | Op       |Msg_type| Msg_text                         |
    +--------------+----------+--------+----------------------------------------------------------------+
    | zuqiu.table1 | optimize | note  | Table does not support optimize, doing recreate + analyze instead|
    | zuqiu.table1 | optimize | status| OK                                                               |
    +--------------+----------+-------+----------------------------------------------------------------+
    2 rows in set (12 min 54.47 sec)

限制

1. OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用
2. 这个操作会使MySql锁定表
3. InnoDB引擎的表分为共享表空间和独享表空间表。独享表空间的表无法进行optimize操作， 因为数据删除时会重组索引并释放对应空间。

可通过以下SQL来查看是否开启独享表空间:

show variables like 'innodb_file_per_table';

MySQL [zuqiu]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

show table status 查看效果

查看前后效果可以使用show table status命令,例如show table status from [database] like '[table_name]';
返回结果中的data_free即为空洞所占据的存储空间。

 show table status like "table_name";

返回结果字段参数说明：

1.Name 表名
2.Engine: 表的存储引擎。旧版本中，叫Type,而不是Engine
4.Row_format 行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。Compressed的行则只在压缩表中存在
5.Rows表中的行数。对于MyISAM和其他一些存储引擎，这个值是精确的，对于事务性引擎（innodb），这个值通常是估算的。
6.Avg_row_length 平均每行包括的字节数
7.Data_length 整个表的数据量(单位：字节)
8.Max_data_length 表可以容纳的最大数据量
9.Index_length 索引占用磁盘的空间大小
10.Data_free 对于MyISAM引擎，标识已分配，但现在未使用的空间，并且包含了已被删除行的空间。
11.Auto_increment 下一个Auto_increment的值
12.Create_time 表的创建时间
13.Update_time 表的最近更新时间
14.Check_time 使用 check table 或myisamchk工具检查表的最近时间
15.Collation 表的默认字符集和字符排序规则
16.Checksum 如果启用，则对整个表的内容计算时的校验和
17.Create_options 指表创建时的其他所有选项
18.Comment 包含了其他额外信息，对于MyISAM引擎，包含了注释徐标新，如果表使用的是innodb引擎 ，将现实表的剩余空间。如果是一个视图，注释里面包含了VIEW字样。

MySql 表的 Optimize 优化 https://www.xiexianbin.cn/database/mysql/2020-05-06-mysql-optimize/index.html
mysql学习之-show table status（获取表的信息）参数说明 https://www.cnblogs.com/andy6/p/6182358.html