Author: Plinux
当你对InnoDB进行修改操作时，例如删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行，但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。如果表结构中包含动态长度字段，那么这些空洞甚至可能不能被InnoDB重新用来存新的行，因为空间空间长度不足。 有些用户可能会使用 OPTIMIZE TABLE 或者 ALTER TABLE <table> ENGINE=InnoDB 来重建这些表，但是这样会导致表的拷贝，如果临时空间不足甚至不足以进行一次 OPTIMIZE TABLE 操作。并且如果你用的是共享表空间方式，OPTIMIZE TABLE 会导致你的共享表空间文件持续增大，因为整理的索引和数据都追加在数据文件的末尾。
从MariaDB 10.1开始，MariaDB把Facebook的碎片整理代码合并进来了，并且把所有代码都调整到InnoDB/XtraDB层去实现，因而只需要使用现成的 OPTIMIZE TABLE 命令就行。开启新的整理算法需要把下面的配置加到 my.cnf 配置文件中：
[mysqld]
innodb-defragment=1
这样配置以后新的碎片整理功能就会替代原有的 OPTIMIZE TABLE 算法，不会有新的表生成，也不需要把旧表的数据拷贝到新表。新的算法会载入 n 个页面尝试把上面的记录紧凑的合并到一起，从而让页面存满记录，然后释放掉完全空了的页面。
set @@global.innodb_file_per_table = 1;
set @@global.innodb_defragment_n_pages = 32;
set @@global.innodb_defragment_fill_factor = 0.95;
CREATE TABLE tb_defragment (
pk1 bigint(20) NOT NULL,
pk2 bigint(20) NOT NULL,
fd4 text,
fd5 varchar(50) DEFAULT NULL,
PRIMARY KEY (pk1),
KEY ix1 (pk2)
) ENGINE=InnoDB;
delimiter //;
create procedure innodb_insert_proc (repeat_count int)
begin
declare current_num int;
set current_num = 0;
while current_num < repeat_count do
INSERT INTO tb_defragment VALUES (current_num, 1, REPEAT('Abcdefg', 20), REPEAT('12345',5));
INSERT INTO tb_defragment VALUES (current_num+1, 2, REPEAT('HIJKLM', 20), REPEAT('67890',5));
INSERT INTO tb_defragment VALUES (current_num+2, 3, REPEAT('HIJKLM', 20), REPEAT('67890',5));
INSERT INTO tb_defragment VALUES (current_num+3, 4, REPEAT('HIJKLM', 20), REPEAT('67890',5));
set current_num = current_num + 4;
end while;
end//
delimiter ;//
commit;
set autocommit=0;
call innodb_insert_proc(50000);
commit;
set autocommit=1;
在建表和插入操作之后，我们可以在 INFORMATION_SCHEMA 中看到如下信息：
select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY';
Value
313
select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1';
Value
72
select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed');
count(stat_value)
0
select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split');
count(stat_value)
0
select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag');
count(stat_value)
0
SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB' and table_name like '%tb_defragment%';
table_name data_free_MB table_rows
tb_defragment 4.00000000 50051
SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY';
table_name index_name sum(number_records) sum(data_size)
`test`.`tb_defragment` PRIMARY 25873 4739939
SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1';
table_name index_name sum(number_records) sum(data_size)
`test`.`tb_defragment` ix1 50071 1051775
现在如果我们删掉 3/4 的记录就会在页面中留下很多空洞，然后我们通过 OTPTIMIZE TABLE 执行整理操作：
delete from tb_defragment where pk2 between 2 and 4;
optimize table tb_defragment;
Table Op Msg_type Msg_text
test.tb_defragment optimize status OK
show status like '%innodb_def%';
Variable_name Value
Innodb_defragment_compression_failures 0
Innodb_defragment_failures 1
Innodb_defragment_count 4
执行完之后我们可以看到有些页面被释放掉了，有些页面被合并了：
select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY';
Value
0
select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1';
Value
0
select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed');
count(stat_value)
2
select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split');
count(stat_value)
2
select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag');
count(stat_value)
2
SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB';
table_name data_free_MB table_rows
innodb_index_stats 0.00000000 8
innodb_table_stats 0.00000000 0
tb_defragment 4.00000000 12431
SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY';
table_name index_name sum(number_records) sum(data_size)
`test`.`tb_defragment` PRIMARY 690 102145
SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1';
table_name index_name sum(number_records) sum(data_size)
`test`.`tb_defragment` ix1 5295 111263