根据****热计费项目生产环境上，ibd文件异常大，借机梳理表碎片清理和表空间收缩的知识点
1、碎片清理的好处
降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率
OPTIMIZE TABLE table_name;对myisam表有用,对innodb也有用，系统会自动把它转ALTER TABLE table_name ENGINE = Innodb; 这是因为optimize table的本质，就是alter table，所以不管myisam引擎还是innodb引擎都可以使用OPTIMIZE TABLE回收表空间.
注意:
1)、表空间管理的类型：共享表空间ibdata1(5.5默认)和独立表空间*.ibd文件(5.6默认，即innodb_file_per_table=ON)
2)、每个表的表空间存放的是该表自己的数据和索引；
3)、drop table自动回收表空间，删除大量数据后可以通过alter table xx engine = innodb;回收空间;
2、关于OPTIMIZE的一些用法(标红的为应用场景)和描述
OPTIMIZE TABLE tbl_name [, tbl_name] ...
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，即delete或update,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
3、碎片产生的原因
删除时留白, 插入时尝试使用留白空间，当删除后并未将所有留空的都插入数据,既可以认为未被立即使用的留空就是碎片；
备注：
1) MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可;
2) OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可;
3) 在OPTIMIZE TABLE 运行过程中，MySQL会锁定表;
4) 默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE;
4、查相关碎片的命令
-- 列出所有已经产生碎片的表
select table_schema db, table_name, round(data_free/1024/1024, 2) data_free, engine,table_rows, round((data_length+index_length)/1024/1024,2) length
from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;
-- 查询并计算碎片率
select table_schema db, table_name, engine,table_rows, round(data_free/1024/1024, 2) data_free_M, round((data_length+index_length)/1024/1024,2) length_M , round(data_free/(data_free + data_length+index_length),2) rate
from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0 order by data_free_M desc ,rate desc;
--查看某张表的碎片率
mysql> show table status like 't_exception_log202005';
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
| t_exception_log202005 | InnoDB | 10 | Dynamic | 61360 | 18294 | 1122566144 | 0 | 0 | 6291456 | NULL | 2020-07-31 01:56:57 | NULL | NULL | utf8_general_ci | NULL | | 异常信息日志表 |
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
查询结果中：
Data_length : 数据的大小
Index_length : 代表索引的数量
Data_free : 代表碎片数量（指占用page的大小）
5、本地模拟*.ibd文件缩小
5.1 创建新表frag_test
create table frag_test (id int auto_increment primary key, c1 varchar(64));
5.2 利用存储过程插入数据
mysql> delimiter $$
mysql> create procedure insert_frag_test(IN START INT(10),IN max_num INT(10))
-> begin
-> declare i int default 0;
-> set autocommit = 0;
-> repeat
-> set i = i + 1;
-> insert into frag_test(id,c1) values((START+i),"this is a test i");
-> until i = max_num
-> end repeat;
-> commit;
-> end $$
mysql> delimiter ;
mysql> call insert_frag_test(1, 2000000);
mysql> call insert_frag_test(2000002, 10000000);
ERROR 1534 (HY000): Writing one row to the row-based binary log failed
这是因为内存不足导致的binlog cache size不够不能写入binlog，导致语句无法执行
在配置文件中调整binlog_cache_size和max_binlog_cache_size参数的值，改大一点
查看参数：
mysql> show variables like '%binlog_cache_size%';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| binlog_cache_size | 4194304 |
| max_binlog_cache_size | 536870912 |
+-----------------------+-----------+
mysql> set global binlog_cache_size=157810688;
5.3 查看ibd文件以及碎片量
[mysql@db1 test01]# du -sh *
4.0K db.opt
12K frag_test.frm
308M frag_test.ibd
12K t1.frm
96K t1.ibd
mysql> show table status like 'frag_test';
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| frag_test | InnoDB | 10 | Dynamic | 7086834 | 41 | 295469056 | 0 | 0 | 5242880 | 7104392 | 2020-10-16 10:15:43 | 2020-10-16 10:49:22 | NULL | utf8_general_ci | NULL | | |
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+---------
5.4 删除数据，查看ibd文件大小以及碎片量
mysql> delete from frag_test where id =20;
mysql> delete from frag_test where id > 24 and id < 30;
mysql> delete from frag_test where id > 200 and id < 230;
mysql> delete from frag_test where id > 2220 and id < 2560;
mysql> delete from frag_test where id > 30000 and id < 50000;
mysql> show table status like 'frag_test';
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| frag_test | InnoDB | 10 | Dynamic | 7066457 | 41 | 295469056 | 0 | 0 | 227540992 | 7104392 | 2020-10-16 10:15:43 | 2020-10-16 10:49:22 | NULL | utf8_general_ci | NULL | | |
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
在删除数据的过程中看到frag_test.ibd文件大小并没有变化
5.5、执行优化
mysql> optimize table frag_test;
查看ibd文件，明显缩小
[mysql@db1 test01]# du -sh *
4.0K db.opt
12K frag_test.frm
104M frag_test.ibd
12K t1.frm
96K t1.ibd
mysql> show table status like 'frag_test';
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| frag_test | InnoDB | 10 | Dynamic | 1993875 | 50 | 100253696 | 0 | 0 | 4194304 | 7104392 | 2020-10-16 14:00:30 | NULL | NULL | utf8_general_ci | NULL | | |
+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
发现Data_length和Data_free都有变小
5.6 删除剩余所有的数据
目前的数据量
mysql> select count(*) from frag_test;
+----------+
| count(*) |
+----------+
| 1999002 |
+----------+
1 row in set (0.29 sec)
全部删除mysql> delete from frag_test;
全部删除后ibd文件依旧是104M
通过mysql> show table status like 'frag_test';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| frag_test | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 103809024 | 7104392 | 2020-10-16 14:00:30 | 2020-10-16 14:06:54 | NULL | utf8_general_ci | NULL | | |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
发现Data_length变小和Data_free都有变大
执行mysql> optimize table frag_test;
查看ibd文件
[root@db1 test01]# du -sh *
4.0K db.opt
12K frag_test.frm
96K frag_test.ibd
12K t1.frm
96K t1.ibd
执行mysql> show table status like 'frag_test';
*************************** 1. row ***************************
Name: frag_test
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 7104392
结论：
1）碎片清理可以使用optimize table table_name,手动触发数据页合并；
2）optimize table执行过程中会锁表，会产生临时表，占用一定的空间，会影响主从延迟；
补充：
数据页合并有自动触发和手动触发;
手动触发：optimize table
自动触发：依赖于数据页合并临界值（MERGE_THRESHOLD）；
MySQL InnoDB 表数据页或者二级索引页（简称数据页或者索引页）的合并与分裂对 InnoDB 表整体性能影响很大；数据页的这类操作越多，对 InnoDB 表数据写入的影响越大。
MySQL 提供了一个数据页合并临界值（MERGE_THRESHOLD），在某些场景下，可以人为介入，减少数据页的合并与分裂。在 InnoDB 表里，每个数据页默认16K 大小，默认 MERGE_THRESHOLD 值为 50，取值范围从 1 到 50，默认值即是最大值。也就是当页面记录数占比小于 50% 时，MySQL 会把这页和相邻的页面进行合并，保证数据页的紧凑，避免太多浪费。
注意：
1、DELETE执行时，当达到阈值后page会合并，被合并的page会并标记为空闲页，留着以后用，并不会释放。所以DELETE后，ibd是不会变小的(truncate会变小)。使用optimze，等于表重构，所以ibd会变小。
2、对于Innodb 引擎，一般optimze是在开始和结束阶段需要元数据锁，中间阶段是可以进行DML操作的。