optimize table

optimize table对于innodb来说，相当于alter table ... force，所做的事情是：拷贝数据进行表重建，更新索引，释放磁盘空间。回收的磁盘空间主要来自于数据的删除，以及变长字段（varchar varbinary blob text）的修改。

默认算法

默认的optimize table行为因为是拷贝表，然后重建，所以它有几个特点：

1. 慢，全表拷贝自然是慢

2. 耗费空间多，因为要全表拷贝，自然得占用该表数据尺寸两位的空间

3. 可在线执行，数据重建好再切换回来，所以不会影响数据库的正常访问

如果表数据只点磁盘空间的一半以下，那么可以大胆执行该优化命令，拷贝数据会放在表数据文件同目录下，名字例如：#sql-ib726-266430743.ibd

如果表数据占到磁盘空间一半以上，甚至磁盘将近占满，那就得小心了，一旦命令执行，数据开始拷贝，是结束不掉的，MySQL(Mariadb)如果写满磁盘，也不会报错退出，它会一直等待空间，没有命令可以中止它，除非kill -9强杀MySQL。

你可能会想到，挂载一个新磁盘，然后使用ALTER TABLE <table> ENGINE=InnoDB FORCE DATA DIRECTORY='/mnt/new_disk/'，不过很不幸，对于这个语句，官方文档说明了不会识别DATA DIRECTORY。

总之，磁盘冗余一半是个好习惯。我也知道，对于小型创业公司来说，2TB装数据，再2TB冗余，是有点奢侈的。

新算法

mariadb的10.1.1版本添加了facebook的补丁，Percona-XtraDB支持另一种optimize table算法，它只挪动需要挪动的page，不会重新创建表，也不会拷贝所有数据，所以它的特点正好相反：

1. 快，一个300万条记录的复杂表，在修改删除极少情况下，使用默认optimize table要2分多钟，使用该算法只需要20秒。实际选择时，应该根据修改删除比例来考量。

2. 耗费空间少，因为只是挪动page，所以磁盘占用不会变大

3. 不能在线执行，算法要频繁锁表，可能还是锁库，实际测试时，命令一执行，整个Mariadb就不响应，连终端连接就无法再建立

与新算法相关的全局变量如下：

MariaDB [color]> show variables like 'innodb_defragment%';
+--------------------------------------+----------+
| Variable_name                        | Value    |
+--------------------------------------+----------+
| innodb_defragment                    | OFF      |
| innodb_defragment_fill_factor        | 0.900000 |
| innodb_defragment_fill_factor_n_recs | 20       |
| innodb_defragment_frequency          | 40       |
| innodb_defragment_n_pages            | 7        |
| innodb_defragment_stats_accuracy     | 0        |
+--------------------------------------+----------+

开启新算法：

select @@global.innodb_defragment;
set @@innodb_defragment=ON;

当磁盘空间不足时，optimize table原始算法行不通，新算法又不能在线执行，只能通过主从切换，来回切流量，才能完成平滑过渡。

其他方式

可能还会有人采用mysqldump，再drop，再执行dump文件的办法，此方法的特点如下：

1. 慢，mysqldump还行，但是再执行dump文件就很慢了，都是sql语句的执行，一条一条插入

2. 耗费空间多，显而易见

3. 不能在线执行，mysqldump就会导致锁表，drop表再创建，明显也是不能在线执行的

总结：公司图片库优化，发现磁盘空间快不足了，删除无用图片，想回收磁盘空间，辗转N种方案而不得解，一些信息记录于此。