1.碎片化介绍
在MySQL的Innodb引擎表设计中，对删除一些数据的操作，只是被标记为“已删除”，而不是真正的从物理文件中删除，因而空间也没有真的被释放回收。虽然MySQL中有Purge线程会异步清理这些没用的数据，但是依然没有把这些空间释放出来还给操作系统重新使用。因此，数据页中存在很多空洞。当然，有新的数据插入时，会尽可能使用这些空洞的空间，进行覆盖，这样做既节省又能再次利用。
但往往不是那么尽人如意，当MySQL数据库负载高，或或算法不足以满足时，会顾不上原空间利用，只会开辟新的空间。这样导致的结果是，实际数据量就只有几万行，但底层数据ibd文件会非常大。跟实际空间不成对比。这就是MySQL表碎片化场景。普遍场景下对整体影响不大，但过多的碎片化会导致表扫描所花费的时间比“应该”花费的时间更长，底层IO会更高。
2.碎片的影响
- 空间浪费：空间浪费不用多说，碎片占用了大量可用空间。
- 读写性能下降：由于存在大量碎片，数据从连续规则的存储方式变为随机分散的存储方式，磁盘I0会变的繁忙，数据库读写性能就会下降。
- 执行计划不准确导致SQL语句性能下降。因为样本sample采取的页里包含空洞，导致Cardinality信息不准确，执行计划选择错误。
3.容易产生大量碎片的场景
在MySQL使用经验中，容易大量碎片出现的情况如下：
1.频繁大量随机的DELETE操作会产生会不连续的空白空间，这些空间使用率非常底。
2.大量的UPDATE，也会产生文件碎片化，Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（Page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。
4.如何查询表的碎片化信息
MySQL提供系统表和工具可以查看碎片信息。
1）命令行统计信息。
通过SHOW STATUS 命令查看表的情况：
mysql>SHOW TABLE STATUS LIKE 'attendance'G;
*************************** 1. row ***************************
Name: attendance
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 2048
Data_length: 253040000
Max_data_length: 0
Index_length: 62040000
Data_free: 102400
Auto_increment: NULL
Create_time: 2023-09-18 18:08:38
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
查询information_schema.TABLES获取表的碎片化信息：
mysql> SELECT CONCAT(table_schema, '.', table_name) AS table_name,
(data_length+index_length) / 1024 / 1024 AS total_mb,
data_free / 1024 / 1024 AS data_free_mb,
data_free * 100/(data_length+index_length) AS data_free_percent,
CURDATE() AS today
FROM information_schema.tables
WHERE table_schema not in ('mysql','sys','information_schema','performance_schema')
ORDER BY data_free_percent desc limit 5;
+--------------------+------------+--------------+-------------------+------------+
| table_name | total_mb | data_free_mb | data_free_percent | today |
+--------------------+------------+--------------+-------------------+------------+
| book.attendance | 323.031250 | 100.000000 | 33.000 | 2023-12-18 |
| book.customers | 300.031250 | 90.00000000 | 0.3000 | 2023-12-18 |
| book.dataseta | 120.015625 | 4.00000000 | 0.0333 | 2023-12-18 |
| book.employees | 79.0312500 | 1.00000000 | 0.0100 | 2023-12-18 |
| book.jemp | 50.0468750 | 1.00000000 | 0.0100 | 2023-12-18 |
| book.members | 42.0468750 | 1.00000000 | 0.0100 | 2023-12-18 |
| book.opening_lines | 40.0156250 | 1.00000000 | 0.0100 | 2023-12-18 |
+--------------------+------------+--------------+-------------------+------------+
10 rows in set, 10 warnings (0.01 sec)
备注：这有可能存在不准确信息，最好在到数据目录查看文件实际大小。
2）MySQL工具innochecksum分析碎片
innocchecksum是官方提供的InnoDB引擎文件的校验工具。该工具读取InnoDB表空间文件，计算每个页面的校验和，将计算出的校验和与存储的校验和进行比较。需要保证ibd文件没有文件锁，最好的方式是先关闭mysqld进程。
显示各种页类型的计数：
shell> innochecksum --page-type-summary ./attendance.ibd
File::./attendance.ibd
================PAGE TYPE SUMMARY==============
#PAGE_COUNT PAGE_TYPE
===============================================
625 Index page #索引页
1 SDI Index page #数据字典也
0 Undo log page #Undo页
1 Inode page #索引页
0 Insert buffer free list page #插入缓冲空闲列表页
523 Freshly allocated page #表示还没有使用的页
1 Insert buffer bitmap #插入缓冲位图页
0 System page #系统页
0 Transaction system page #事务数据页
1 File Space Header #文件空间标头
0 Extent descriptor page
0 BLOB page #BLOB数据页
0 Compressed BLOB page #压缩的BLOB页
0 Subsequent Compressed BLOB page
0 SDI BLOB page #SDI BLOB页
0 Compressed SDI BLOB page #压缩的 SDI BLOB页
0 Other type of page #其他页
===============================================
Additional information:
Undo page type: 0 insert, 0 update, 0 other
Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other
备注：需要关注Freshly Allocated Page:可用页
显示表空间中每个页的分布情况：
shell> innochecksum --page-type-dump=/tmp/a.txt ./attendance.ibd
shell> cat /tmp/a.txt
备注：这里的index page都是按照page num的顺序从小到大排列的。records显示每个page的行数。
GARBAGE已经被删除的记录行数。从这里看出是否复用了这个页。
5.如何整理碎片
在MySQL碎片整理，有如下3种技术方式：
- ALTER TABLE tbl_name ENGINE=INNODB
当指定ENGINE子句时，ALTER TABLE会重新构建该表。在现有的INNODB表上运行ALTER TABLE tbl_name ENGINE=INNODB将执行“null”ALTER TABLE操作，该操作可用于对INNODB表进行碎片整理。 - OPTIMIZE TABLE回收未使用的空间并对数据文件进行碎片整理。在对表进行大量更改后，此语句还可以提高使用该表的语句的性能，有时甚至可以显著提高性能。
- 另一种方法是逻辑导出导入，删除原表，然后从新加载数据。可以使用mysqldump方式。
ALTER操作和OPTIMIZE会锁表，属于高危命令，时间长短依据数据量的大小。
按照上诉技术手段，可以在主从节点上轮回切换，进行碎片整理。注意，避免在业务高峰期进行，以减少对系统性能的影响。
总结
对于MySQL碎片情况，官方建议不要经常进行碎片整理，一般根据实际情况，当出现碎片，并导致的性能问题比较严重时，选择合适的时机和合适的方式进行整理。
对于日常碎片化监控，建议先通过系统表统计信息进行监控。当碎片情况比较严重时，使用innochecksum进行二次分析，之后进行碎片整理。
