mysql大数据时的插入和更新的高效方法

      最近有项目,数据量在不断的上升级,数据量突破百万后,一条简单的插入语句,mysql数据库的cpu占用率都达到100%,于是整个系统被卡住,从而造成系统死机.
      这个问题,查了发现是mysql数据库使用简单的数据引擎:Myisam造成.   这个引擎是mysql用得最多的,因为它简单,查询高效,所以被很多人默认使用.
      但是他不支持事务,且insert和delete会造成锁表.当一个百万数据量的表被锁住后,其它请求量一上来,整个系统必然卡死.网上有说换引擎为InnoDB会好,因为它不锁表,它只是行锁,所以插入删除时特别高效,但是他查询慢,不过当时没有试它.
       最后我们使用Load data infile这种方法来解决了问题.
       因为我们需要在一个for循环里,不停的处理业务,处理完就把数据更新或者插入到一个大表中.因为在for里不断操作insert,结果当表数据量大时,再不断的插入数据到这个个,系统会卡死.所以我们想到了把数据缓存起来,等积到一定程度,再一次性写到数据庘中,减少数据庘的操作.于是找到了mysql的load data这个方法.
之前是这样的:

for($i=0;$i<$len;$++){
      $sql = "insert into table(uid,name,value) values($uid,$name,$value)";
      MDB($sql);
}

要改成load data方法如下:

for($i=0;$i<$len;$++){
   $values=array($uid,$name,$value);
   write_tmp_infile($values,$file_tmp);//改成先写到文件.
}
writeGoods_tmp_batchDB($file_tmp);//写完后,再把这个文件直接load到数据库的一个临时表,这个表同大数据量的表的结构完全相同.
dealwithTradeOrder();//再把数据从tmp表,同步进大数据的表.

function dealwithTradeOrder()
{
   $sql = "insert into user(uid,name,value)";
   $sql .= " (SELECT uid,name,value FROM user_tmp a WHERE NOT EXISTS (select uid,name,value from user b where a.uid=b.uid))";
   MDB($sql);//把增量的数据一次性插入到大数据的表.
   $sql = "select * from user_tmp";
   $result=MDB_QUERY($sql);
   $c_result = count($result);
   for($i=0;$i<$c_result;$i++)//再把剩下的数据更新回大表.
   {
      $sql = "update user set ";
      $sql = $sql." name='".$result[$i]->name."',value='".$result[$i]->value."' ";
      $sql = $sql." where uid='".$result[$i]->uid."'";
      $res=MDB($sql);
   }
   $sql = "delete from user_tmp";//清掉临时表
   MDB($sql);
}

function write_tmp_infile($values,$file_tmp)
{
   $fp = fopen("$file_tmp","a+");
   $col ="\t"."$values[0]"."\t"."$values[1]"."\t"."$values[2]"."\\n\r\n"; //记录赋值 
   fwrite($fp, $col, strlen($col)); //插入第一条记录 
   fclose($fp); //关闭文件 
}
function writeGoods_tmp_batchDB($file_tmp)
{
   $sql = "LOAD DATA INFILE '$file_tmp' INTO TABLE user_tmp character set utf8"; 
   MDB($sql);
}

通过使用这种load data infile的处理方法,数据库的效率可以提升100倍.最起码,我之前cpu一直是100%的,现在cpu基本都是1%这样子.现在整个系统这样优化后,准备向一千万的数据量进发.

注意此方法需要使用root权限,即数据庘帐号要写root来访问数据庘.