针对单一数据表进行扩容时优化速度的策略比较

    现在我们的许多初具规模的网站都会用到信件。其中信件的数据库存储结构大致就是从谁(fromuid)发向谁(touid),还有一个此条消息的发 送时间(addtime)。如果你有比较成熟的思路，你会将这个系统设计成发信箱和收件箱。当有用户发送一封信给一个特定用户时，会在发件箱保存一条记 录，在收件箱保存一条记录。如果邮件很多，那么你还会把发件箱做hash，将邮件散列到不同的表中，一保证每张数据表不会特别的大。但如果这些你都没有考 虑到，或者当初你认为功能上没需要，把所有的数据都放在一张表中呢？那么迁移会是一个很麻烦的事情，尤其是在这个数据表已经为你提供了稳定的线上服务，且不能中断服务时。不断增加的新记录和库中的老记录将成为数据迁移的负担。这时你需要在最短的时间内将数据搬家，尽可能做到无缝切换，并且保证数据不能有任何丢失。

下面我们来开始针对数据表在扩容优化上的速度比较吧。

    假设你的表结构是个3列的数据表，字段如下：

    fromuid    touid    addtime

    这个数据表随着你的用户和访问量的增加，会急速增加。甚至会使你的程序在这里成为瓶颈。那么没办法，你需要对这张表进行扩容。而我下面要说的就是扩容的方案和一些在导出数据时操作的细节，这些细节，能让你优化表的速度得到很大的提升^_^

 

    我们假设这个数据表是InnoDB类型的，数据量超级大，100G吧（不要害怕，这里面还有索引呢），但我们就假设他是100G。首先你要先知道文件具体有多大，然后明确你想将每张表控制在多大（极限状态），这样做除法，你就能知道你要分多少张表了。这里我们假设分成128张数据表。加上我们将单一表结构变成了既有发信箱又有收件箱，那么原来的一张表就会变成128*2=256张表。

 

    如果这是一个线上系统，你肯定要考虑到热同步，即你不可能让你的产品在线上一停就一天，不让用户发信吧，那么就需要保证缩短你切换新表和旧表的时间，再这之前要做一系列的工作。这个不是我这次要说的重点，以后再说。我想说的是对于这么一个很大的数据，我们平常是如何切分到新库呢？

 

那么我们来构造点儿数据吧。

假设fromuid是10位的，touid是10位的，addtime是一个19位的完整时间，每列之间一个/t占1位，结尾一个/n占1位。每行就是43字节。我们构造一个1亿行的数据。那么大小正好是4G，这个文件叫做test_data.log。他使我们下面测试的数据源。

 

1、切分文件。

首先，我们要将100G数据文件的散列字段都倒出(DBA可不会帮你把文件分割成好几个再发给你，这是你需要做的工作）。

 

方法a：切分文件我们第一个想到的是linux系统自带的split命令。假设我们每2千万行切割一个程序，这样的话，1亿行正好是5个文件。

[root@localhost test_data]# time split -l 20000000 test_data.log new

real    0m15.128s
user    0m3.411s
sys     0m10.516s

这个经过测试就是最高效的，毕竟是系统级调用。我也是在同事质疑这个以后，而没有直接用他切。唉~

 

方法b：同事说给我两台高性能的机器，内存32G，其中16G划分成内存盘。这样的话，切割文件的策略就变成，我可以一次性将2千万数据先读入16G内存盘，然后再执行mv指令将该文件移动到磁盘。

这里我还是用的每2千万行数据存储一个文件，也就意味着在内存中只读入了不到1G的内容。所以虽然这个方法的速度比split要慢，但我想，当我的真实数据到了以后，一次把16G内存都读满，速度应该有大幅提升。

[root@localhost ~]# time php split_data.php
real    2m8.059s      <--这个数字随着放入内存的记录的增长，应该能减少很多。期待真实数据切分时的数值
user    1m55.013s
sys     0m12.888s

 

方法c：读一条，往文件中写一条，汗~这应该是最笨的一种方法了，不知道要写到猴年马月，文件IO的次数将成为运行速度得最大瓶颈。（不推荐）

 

 

这里有个细节需要指出：本来我最早写的程序时采用error_log的第3种方式，追加到文件的。但是同事说怀疑error_log在系统级是采用open,write,close三部操作的。还是建议我用fopen来创建句柄，然后不关闭句柄，来减少系统调用。所以我没有测试调用error_log写文件会比我现有的方式慢多少。但网上说error_log在数据多了以后，好像性能会下降。

 

 

2、hash到文件

方法a:每读一行，根据fromuid进行一次hash，根据touid再进行一次hash，然后分别通过error_log或者fwrite方式写入一条。这样最省事儿，程序逻辑也最简单。但是效率就不能保证了，相当于我们要有2亿次对文件的读写。天~~~   （不推荐）

 

方法b：我们应该充分发挥内存的功效，一次性尽可能多的读入数据。还记得我上面把一个文件拆分成5个文件了么，如果这些文件现在都小于我们的内存上限，那么就一次性将文件全读到内存中，处理完了，再写入新的文件，这样我们只有256此IO操作（至少比写2千万次好很多吧）。

     当然这里要注意，如果你内存不够大，只分5个文件不能将一个文件一次性读入内存的话，那么此时在hash程序上也要控制一次读入到内存的行数，而你如果把每个文件的大小都控制在内存上限允许的范围的话，又可能造成第一部切割文件时生成多个文件。理论上，第一步后我们并不希望有太多的文件，之所以切割，为的也是可以将这些文件通过rsync方法推送到其他服务器上，然后多台服务器上，这样你的时间还能缩短,时间=总时间/服务器台数。

 

按照方法b：每2千万读入内存，再进行hash，其中hashTable的名称作为数组的key，每个值都进行连接。而后循环一次数组，就把所有内容写到hashTable对应的文件中了。

测试一下效率：

[root@localhost ~]# time php hash_data.php

real    18m14.634s
user    17m57.038s
sys     0m17.429s

 

  

这样的话，我们对一个4G的文件进行扩容，到切分文件这步，只需要20分钟。如果第一部用split的方法，应该能控制在18分钟。别忘了，我们可有两台服务器，第一部切分完的数据分给另外服务器一些，不就变成9分钟了。按照我之前的估计，我们有30G数据的情况下，应该30G/4G*9=67.5min=1小时。快吧。

 

所以选4G作为内存的一个考量，也是因为大家一般用的机器大概都是4核8G什么的，所以这个参考数据更有意义。

 

给我的机器是8核超线程，32G内存的机器，我肯定会把之前说的每2千万行操作一次变成7千万行的。这样的速度期待吧。哈哈。

 

 

 

3、文件导入到数据库

经过hash，每个文件的大小可以控制在1G以下，所以我们这里直接用mysqlimport这个命令。

在前两步生成的文件都是通过/t分隔的。mysqlimport命令可以稳定快速的录入到对应的数据表中。（注，如果你的文件名称叫from_**.sql，那么导入时，会自动将数据录入到from_**表中）。

 

导入过程中可以通过下面这个方法给导入数据提速：

在导入开始set autocomit=0;（ 默认是1）

在导完后set autocomit=1;

此方法会导致磁盘IO很高，负载也会极高。线上有服务不能用！！！！

写完后，听说还有同事倒过960G的~挑战啊，看看我以后有没有机会来一次吧，哈哈。