[MySQL案例]之Discuz大表拆分

背景

相信很多用discuz搭建论坛服务的同学都有这个体验，整个数据库表都是MyISAM引擎，一旦帖子数量到一定量级的时候神马操作都是巨慢无比（MyISAM的各种缺点这里暂且不提）。以此为背景，我们打算对discuz论坛进行改版，简单说来就是彻底抛弃原有的归档功能直接分100张表，按照帖子ID取模分发数据，然后把新表都改成InnoDB引擎。

以这件事为背景，对这次升级过程中数据库的操作做一个记录以及简单分析。抛开前期准备，后期处理等等步骤，这里我们就说下中间的重要环节。

1.在帖子表里面新增一个字段，且加一条索引
2.更新新增字段的值为：mod(tid,100)+1
3.创建一个tempfs分区（需要比帖子表.MYD文件大）
4.使用select into oufile并发导出数据
5.增大innodb_buffer_pool_size,max_allowed_packet，缩小key_buffer_size
6.使用load data infile并发导入数据

中间的数据分表的核心步骤大概就是这么几步，下来我们逐一来讲。

分析

方案的演化

整个分表的方案变化大概是这样的：

第一个方案：由开发同学直接写程序逐条导入，但是马上被我们pass了，1亿四千万行数据逐行插入在时间上是不可接受的。
第二个方案：mysqldump -w，但是考虑到新表是innodb需要新增一个自增主键，这样导入会有问题，所以第二个方案也被pass了。
第三个方案：select into outfile + load data infile，但是这里同样存在一个问题：MySQL不支持函数索引，所以mod(tid,100)+1=x这样是走不了索引。最后只能使用折中的临时方案：新增一个临时字段xxid用来存放mod(tid,100)+1计算后的值，select into outfile的时候直接按xxid查询就可以走索引了。

DDL & UPDATE的优化

一般熟悉数据库的人都有这么一个认知：在你大量更新或者插入数据的时候，最好先删除或者禁用掉索引，等数据插入或者更新完成之后再创建索引，但是这里我们确实先创建了索引再更新数据，这是为什么？

我们来看下这三种方案：

方案1：alter table t add col, add index; update table set xxid1=xxx;
方案2：alter table t add col, add index; alter table t disable keys; update table set xxid1=xxx; alter table t enable keys;
方案3：alter table t add col; update table set xxid1=xxx; alter table t add index;

按照一般的习惯我们会选择第二种或者第三种方案，因为它们符合优化规则，在批量更新或者插入数据前先不要创建索引或者先禁用索引。其中方案二可能是最优的，因为MyISAM引擎对disable/enable keys是有优化的，我们来看这么一段说明：

ALTER TABLE…DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。然后使用ALTER TABLE … ENABLE KEYS重新创建丢失的索引。进行此操作时，MySQL采用一种特殊的算法，比一个接一个地插入关键字要快很多。因此，在进行成批插入操作前先使关键字禁用可以大大地加快速度。

那么我们大致可以得出方案2>方案3>方案1，但是从下图的测试结果看却不是这么一回事？
image

  1. 这张表原本就存在1100W行记录，属于一张不小的表，而且在这次操作中需要新增一个字段，也就是第一个操作都是一个ddl动作；
  2. 在MySQL里面ddl动作是可以合并的，合并以后只会产生一次临时表动作，因此1次ddl耗时绝对小于2次ddl耗时；
  3. ALTER TABLE…DISABLE/ENABLE KEYS针对的是MyISAM（非InnoDB表）的所有非唯一索引，注意是所有而不是更新目标字段的的索引，而本次测试表中除了主键以外一共有6条非唯一索引；

基于以上几个原因，所以实际的结果是方案1>方案2>方案3。另外为了验证，我么干掉了其他5条非唯一索引，enable keys耗时从161秒降低到45秒，也就是说如果在一个少量的非唯一索引的环境中，方案二才是最优选择。

Ps：从上面实验数据确实可以看出，有没有索引对数据更新/插入确实存在很大的影响，对一个定长4字节字段加一条索引，数据更新的效率就差了超过100%了，所以以后类似的问题大家需要多多注意。

还有一点要说明的是

我们在ddl以及update的时候没有对数据库做任何buffer优化设置，其实在一开始的时候我一直觉得加大：key_buffer_size、sort_buffer_size、read_buffer_size、tmp_table_size、max_heap_table_size几个参数可以加速ddl跟update的效率，实际上经过几次测试几乎是没有提升的。key_buffer_size只是缓存了索引数据，其他几个buffer分别是排序区缓存、表顺序查询缓存以及临时表大小设置，不懂为什么MyISAM为什么这样做不能加速ddl，还有如果是InnoDB呢（读不懂源码只能靠猜测，真蛋疼）？我一直以为是可以的，找个时间要做个实验看看。

为什么使用tmpfs

使用tempfs来装载MyISAM的数据文件，能够加速数据导出效率，而且提升超过100%。
一开始也没想到这个方法，只是在测试的时候发现为什么总是第一次效率很低，第二次效率就非常高，提升超过100%。首先我想到的是key_buffer，但是key_buffer只是缓存了索引数据，为了验证重启了数据库再测试发现效率还是高导出速度还是很快。如果不是key_buffer，那是可能就是Linux的cache。为了验证这个想法，我把cache释放掉，再执行导出操作，果然又变成第一次那样很慢。

1
sync && echo 3 > /proc/sys/vm/drop_caches;

但是还是存在问题，本地文件导入到cache中的只有访问的部分，也就是说每次执行select into outfile操作都是很慢，只有第二次执行相同数据导出的时候效率才会提升，这样一来对我们的操作其实是没有帮助的，除非我们能够事先把整个数据文件导入到内存中。
于是就挂载了一个tmpfs，然后把.MYD文件丢进去再link到datadir，再测试果然效率刚刚的。

1
2
3
4
5
mkdir /home/memdir
mount tmpfs /home/memdir/ -t tmpfs -o size=10g
cp pre_forum_post_2.MYD /home/memdir/
mv pre_forum_post_2.MYD pre_forum_post_2.MYD.backup
ln -s /home/memdir/pre_forum_post_2.MYD  ./

数据导出

开始导出，这个没什么好说的，直接写了个脚本开了5个并发进程把数据分别写到1~100这100个文件中。

修改数据库配置

后面就是InnoDB的天下了，MyISAM从此退出江湖，所以要修改下buffer。另外加大max_allowed_packet是怕有的单个outfile文件的value过大，以防万一（刚好前几天因为max_allowed_packet设置的问题导致一个数据库恢复测试失败，这次还是小心为上）。

数据导入

也没什么好说的，一样写个脚本开了5个并发进程在写数据，可以关闭binlog加速数据加载。需要一说的的是新表多了一个自增ID，因此load的时候需要把新表除了新ID以外的所有字段名写上，费了点功夫。

总结

这个discuz数据库的帖子表总共约72G+的数据文件+索引文件，约一亿四千万行数据，我们分四个数据库节点并行操作，预计需要花近3个小时才能完成分表操作（不包含前期准备/备份，后期测试等等部分耗时）。
中间主要是通过tmpfs加速导出，并行执行加速导出导入操作，最终结果尚可接受，但还存在优化空间。

后续

后续还有一些需要验证测试的有：

  1. MyISAM的ddl以及update能否通过哪些参数设置优化？
  2. InnoDB的ddl以及update能否通过哪些参数设置优化？