Mysql Online DDL 和碎片整理

这里记录线上遇到的一个问题，在线上环境遇到一个千万级的大表占用了太大的存储空间，需要把一部分的数据落地归档。

在删除了相应的数据库记录之后，却发现对应的空间没有被释放，导致数据库可用空间还是不足。

这里刚开始猜想就可能是数据库碎片的问题，去查阅了相关资料之后发现确实是数据库碎片的问题。

所以就需要做数据库的碎片整理，在这里做下笔记。

碎片的产生¶

• 表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；

• 当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；

• 当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

为什么需要释放碎片¶

释放碎片的目的就是减少表数据与表索引的物理空间，并且当碎片产生较多时，即使表的实际数据并不多，但是mysql仍会将其当成一个大表进行操作，在查询的时候需要跳过多个碎片空间，这样就会极大的影响查询性能。

sql查询¶

查看表碎片的大小¶

SHOW TABLE STATUS LIKE '表名';

结果中 Data_free 列就表示碎片的大小

列出所有已经产生碎片的表¶

select table_schema db, table_name, data_free, engine     
from information_schema.tables 
where table_schema not in ('information_schema', 'mysql')  and data_free > 0;

清除表碎片¶

需要注意的是，清除表碎片操作会锁表。所以需要考虑在执行操作的时候对线上业务的影响。

MyISAM 引擎¶

optimize table <表名>

Engine不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。

InnoDB 引擎¶

alter table <表名> engine=InnoDB

同样的，InnoDB 通过修改引擎也可以达到同样的效果。

为了不影响线上业务¶

因为整理碎片的操作会锁表，这期间的写入删除操作无法完成，因为这本质上来说是一个 ddl操作，这对于线上业务来说是不可接受的。

目前InnoDB引擎是通过以下步骤来进行DDL的：

• 按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）

• 在原表上加write lock，阻塞所有更新操作（insert、delete、update等）

• 执行insert into tmp_table select * from original_table ( copy table 操作 )

• rename original_table和tmp_table，最后drop original_table

• 释放 write lock。

我们可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。

所以针对线上大表的操作，常见方法是放在业务的低峰期进行操作。但是这样同样会有丢失数据的可能，所以我们可以利用一些 online-ddl 工具来完成。

ddl 指的是对表的 alter操作，这期间都会进行锁表操作。

而 online-ddl 的原理就是通过触发器保持新表与旧表的数据一致，在最后旧版数据拷贝完成之后再将新表重命名为旧表。

我们可以通过工具 pt-online-schema-change 来完成这一操作。

pt-osc¶

pt-online-schema-change 工具需要先安装 percona-toolkit 工具包 下载地址

其特点是修改过程中不会造成读写阻塞。但是 pt-online-schema-change 也有其限制。

• 首先要修改的表不能存在触发器，因为 pt-online-schema-change 是通过对修改表添加触发器来保持数据一致性，如果原表存在触发器，则会因为相同触发器只能存在一个，导致工具不予执行。

• 其次，如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行。

• 并且在使用之前需要对磁盘容量进行评估，因为 pt-osc 本质上是对数据表的一次复制，所以复制的新表还会占用一定空间，这会增加一倍的空间和索引。

这里给出一下示例的参数:

pt-online-schema-change  
  -h地址
  -P端口号
  -u用户名
  -p密码   
  --database=数据库
  t=表名字
  --charset=utf8 
  --max-lag=300 
  --check-interval=5 
  --alter="ENGINE=InnoDB" 
  --max-load="Threads_running:400" 
  --critical-load="Threads_running:400" 
  --nocheck-replication-filters 
  --alter-foreign-keys-method=auto  
  --execute

这里注意一下 alter 的写法。不需要 ALTER TABLE 关键字。与原始ddl一样可以指定多个更改，用逗号分隔。

• 绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 DELETE和UPDATE 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，DELETE触发器将基于c1列。

• 子句不支持 rename 去给表重命名。

• alter命令原表就不支持给索引重命名，需要先drop再add，在pt-osc也一样。(mysql 5.7 支持 RENAME INDEX old_index_name TO new_index_name )，但给字段重命名，千万不要drop-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online）

• 子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。

• 如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "DROP FOREIGN KEY _fk_foo"

另外，在线上阿里云执行 pt-osc 操作的时候会报一个错。

Can't use an undefined value as an ARRAY reference at /usr/bin/pt-online-schema-change line 7335.

给命令添加一个 --no-version-check 选项就好了。

总结¶

最后整理一下在何时选用源生的dll，何时使用pt-osc。

• 在表数据较大，copy table 成本较高的时候 不适合使用源生dll

• pt-osc 不适合使用在有触发器的表上

• 修改索引、外键、列名时，优先采用源生dll，并指定 ALGORITHM=INPLACE

• pt-osc比online ddl要慢一倍左右，因为它是根据负载调整的

• 不管什么时候都要在业务低峰的时候进行操作

• 特殊情况需要利用主从特性，先alter从库，主备切换，再改原主库

tips¶

哈哈。这篇文章有点灌水了，毕竟只是简单记录了一个工作上遇到的一个小问题。好多文字都是直接 copy 其他的文章。

不过也确实是发现了很多没有注意到的地方，看来还有很多不足的地方啊。

参考文章:

• http://seanlook.com/2016/05/27/mysql-pt-online-schema-change/

• http://blog.51cto.com/dadaman/1957229

• https://blog.csdn.net/xlgen157387/article/details/50728737