MySQL表碎片化的原因
关于MySQL中表碎片化（Table Fragmentation）产生的原因，简单总结一下，MySQL Engine不同，碎片化的原因可能也有所差别。这里没有深入理解、分析这些差别。此文仅以InnoDB引擎为主。总结如有不足或错误的地方，敬请指出。
InnoDB表的数据存储在页（page）中，每个页可以存放多条记录。这些记录以树形结构组织，这颗树称为B+树索引。表中数据和辅助索引都是使用B+树结构。维护表中所有数据的这颗B+树索引称为聚簇索引，通过主键来组织的。聚簇索引的叶子节点包含行中所有字段的值，辅助索引的叶子节点包含索引列和主键列。
在InnoDB中，删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。如果表结构中包含动态长度字段，那么这些空洞甚至可能不能被InnoDB重新用来存新的行，因为空间空间长度不足。关于这个你可以参考博客Overview of fragmented MySQL InnoDB tables。
另外，删除数据就会导致页（page）中出现空白空间，大量随机的DELETE操作，必然会在数据文件中造成不连续的空白空间。而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续。物理存储顺序与逻辑上的排序顺序不同,这种就是数据碎片。
对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。
表的数据存储也可能碎片化。然而数据存储的碎片化比索引更加复杂。有三种类型的数据碎片化。##下面部分内容摘自【高性能MySQL】##
行碎片（Row fragmentation）
这种碎片指的是数据行被存储为多个地方的多个片段。即使查询只从索引中访问一行记录。行碎片也会导致性能下降。
行间碎片（Intra-row fragmentaion）
行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
剩余空间碎片（Free space fragmentation）
剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据。从而造成浪费。
对于MyISAM表，这三类碎片化都有可能发生。但InnoDB不会出现短小的行碎片；InnoDB会移动短小的行并写到一个片段中。InnoDb会移动短小的行并重写到一个片段中。
官方文档14.15.4 Defragmenting a Table关于降低表的碎片化介绍如下（非常简洁，MySQL官方文档往往简洁，信息量大，但是没有详细介绍）：
Random insertions into or deletions from a secondary index can cause the index to become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.
One symptom of fragmentation is that a table takes more space than it “should” take. How much that is exactly, is difficult to determine. All InnoDB data and indexes are stored in B-trees, and their fill factor may vary from 50% to 100%.
从二级索引中随机插入或删除可能会导致索引碎片化。碎片意味着磁盘上索引页的物理排序不接近页面上记录的索引排序，或者64页块中有许多未使用的页面被分配给索引。
碎片化的一个症状是表格占用的空间比“应该”占用的空间多。多少确切地说，很难确定。所有 InnoDB 数据和索引都存储在 B-trees 中，它们的 fill factor 可能在50％到100％之间变化。碎片的另一个症状是像这样的表扫描需要比“应该”花费更多的时间.
查看表的碎片
1. 使用show table [from|in db_name] status like '%table_name%';
mysql> show table from employees status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1176484
Avg_row_length: 86
Data_length: 101842944
Max_data_length: 0
Index_length: 0
Data_free: 39845888
Auto_increment: NULL
Create_time: 2018-08-28 13:40:19
Update_time: 2018-08-28 13:50:43
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
碎片大小 = 数据总大小 - 实际表空间文件大小
- 数据总大小 =
Data_length + Data_length= 101842944 - 实际表空间文件大小 =
rows * Avg_row_length= 1176484 * 86 = 101177624 - 碎片大小 = (101842944 - 101177624) / 1024 /1024 = 0.63MB
2. 查询information_schema.TABLES获取表的碎片化信息。
##查询某个数据库的表碎片化情况
SELECT CONCAT(table_schema, '.', table_name) AS TABLE_NAME
,engine AS TABLE_ENGINE
,table_type AS TABLE_TYPE
,table_rows AS TABLE_ROWS
,CONCAT(ROUND(data_length / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE
,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE
,CONCAT(ROUND((data_length + index_length )
/ ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE
,CASE WHEN data_length =0 THEN 0
ELSE ROUND(index_length / data_length, 2) END AS TB_INDX_RATE
,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB') AS TB_DATA_FREE
,CASE WHEN (data_length + index_length) = 0 THEN 0
ELSE ROUND(data_free/(data_length + index_length),2)
END AS TB_FRAG_RATE
FROM information_schema.TABLES
ORDER BY data_free DESC;
## 查看空闲空间超过50M大小的表
SELECT CONCAT(table_schema, '.', table_name) AS TABLE_NAME
,engine AS TABLE_ENGINE
,table_type AS TABLE_TYPE
,table_rows AS TABLE_ROWS
,CONCAT(ROUND(data_length / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE
,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE
,CONCAT(ROUND((data_length + index_length )
/ ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE
,CASE WHEN data_length =0 THEN 0
ELSE ROUND(index_length / data_length, 2) END AS TB_INDX_RATE
,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB') AS TB_DATA_FREE
,CASE WHEN (data_length + index_length) = 0 THEN 0
ELSE ROUND(data_free/(data_length + index_length),2)
END AS TB_FRAG_RATE
FROM information_schema.TABLES
WHERE ROUND(DATA_FREE/1024/1024,2) >=50
ORDER BY data_free DESC;
整理表的碎片
使用alter table table_name engine = innodb命令进行整理。
root@localhost [employees] 14:27:01> alter table t1 engine=innodb;
Query OK, 0 rows affected (5.69 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost [employees] 14:27:15> show table status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1191062
Avg_row_length: 48
Data_length: 57229312
Max_data_length: 0
Index_length: 0
Data_free: 2097152
Auto_increment: NULL
Create_time: 2018-08-28 14:27:15
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)这其实是一个NULL操作,表面上看什么也不做,实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间.
使用alter table table_name engine = innodb命令进行整理
OPTIMIZE TABLE 会重组表和索引的物理存储，减少对存储空间使用和提升访问表时的IO效率。对每个表所做的确切更改取决于该表使用的存储引擎
OPTIMIZE TABLE的支持表类型：INNODB,MYISAM, ARCHIVE，NDB；它会重组表数据和索引的物理页，对于减少所占空间和在访问表时优化IO有效果。OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长。
OPTIMIZE TABLE后，表的变化跟存储引擎有关。
对于MyISAM， PTIMIZE TABLE 的工作原理如下：
1. 如果表有已删除的行或拆分行（split rows），修复该表。
2. 如果未对索引页面进行排序，对它们进行排序。
3. 如果表的统计信息不是最新的（并且无法通过对索引进行排序来完成修复），更新它们。
对于InnoDB而言，PTIMIZE TABLE 的工作原理如下
对于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE ... FORCE（或者这样翻译：在InnoDB表中等价 ALTER TABLE ... FORCE），它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。当您在InnoDB表上运行时，它会显示在OPTIMIZE TABLE的输出中，如下所示：
mysql>optimize table account;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+-------------------------------------------------------------------+
| test.account | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.account | optimize | status | OK |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.09 sec)
OPTIMIZE TABLE对InnoDB的普通表和分区表使用online DDL，从而减少了并发DML操作的停机时间。由OPTIMIZE TABLE触发表的重建，并在ALTER TABLE ... FORCE的掩护下完成。仅在操作的准备阶段和提交阶段期间短暂地进行独占表锁定。在准备阶段，更新元数据并创建中间表。在提交阶段，将提交表元数据更改。
OPTIMIZE TABLE 在以下条件下使用表复制方法重建表：
1. 启用old_alter_table系统变量时。
2. 启用mysqld --skip-new 选项时。
OPTIMIZE TABLE 对于包含FULLTEXT索引的InnoDB表不支持online DDL。而是使用复制表的方法。
InnoDB使用页面分配方法存储数据，并且不会像传统存储引擎（例如MyISAM）那样受到碎片的影响。在考虑是否运行优化时，请考虑服务器将处理的事务的工作负载：
1.预计会有一定程度的碎片化。 InnoDB仅填充93％的页面，为更新留出空间而无需拆分页面。
2.删除操作可能会留下空白，使页面填充不如预期，这可能使得优化表格变得有价值。
当行有足够的空间时，对行的更新通常会重写同一页面中的数据，具体取决于数据类型和行格式。
高并发工作负载可能会随着时间的推移在索引中留下空白，因为InnoDB通过其MVCC机制保留了相同数据的多个版本。
另外，对于innodb_file_per_table=1的InnoDB表，OPTIMIZE TABLE 会重组表和索引的物理存储，将空闲空间释放给操作系统。也就是说OPTIMIZE TABLE [tablename] 这种方式只适用于独立表空间
除了以上两种方式外, 还可以借助第三方pt-online-schema-change工具也能进行在线整理表结构，收集碎片等操作。
使用shell脚本
#!/bin/sh
socket=/tmp/mysql3306.sock
time=`date +"%Y-%m-%d"`
SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'tablename'"
optimize_table_name=$(/usr/local/mysql/bin/mysql -S $socket -e "$SQL"|grep -v "TABLE_NAME")
echo "Begin Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>/tmp/optimize_table_$time.log
for table_list in $optimize_table_name
do
echo `date +"%Y-%m-%d %H:%M:%S"` "alter table $table_list engine=innodb ...">>/tmp/optimize_table_$time.log
/usr/local/mysql/bin/mysql -S $socket -e "alter table $table_list engine=innoDB"
done
echo "End Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>>/tmp/optimize_table_$time.log
