mysql表列复制,mysql连接时间和内存控制。
有两个表临时表temp_t,和正表table1,都是MyISAM,分别有28W条左右的记录。
cron定时每分钟从网络上获取一些信息,先存储到temp_t(频繁写入),
然后cron定时每10分钟从temp_t复制信息到正表table1(频繁读取,每10分钟写一次)。
temp_t字段 id(auto_increment),title(varchar(200)),content(varchar(500)),date(DATETIME), mykey(tinyint(1) Default 1, 这个来判别是否已经复制到正表,复制过后UPDATE为2)
table1字段 id(没有auto_increment),title(varchar(200)),content(varchar(500)),date(DATETIME)
28W记录,每个数据表大概有300MB,每次更新差不多要2K-3K条记录。
如果直接复制的话,MYSQL连接时间过长,导致MYSQL性能下降table1会出现类似于锁表的现象,查询table1的时间明显加长
require dirname(__FILE__) . '/../connection.php';<br /> mysql_select_db("news",$connextion);<br /> mysql_query("SET NAMES utf8");<br /> $query = mysql_query("SELECT * FROM temp_t where mykey = '1'");<br /> while($rows = mysql_fetch_array($query)){<br /> mysql_query("UPDATE temp_t SET mykey='2' WHERE id='".mysql_real_escape_string($rows['id'])."'");<br /> mysql_query("INSERT INTO table1 (id,title,content,date) values ('".mysql_real_escape_string($rows['id'])."','".mysql_real_escape_string($rows['title'])."','".mysql_real_escape_string($rows['content'])."','".mysql_real_escape_string($rows['date'])."'");<br /> }<br /> mysql_close($connextion);
require dirname(__FILE__) . '/../connection.php';<br /> mysql_select_db("news",$connextion);<br /> mysql_query("SET NAMES utf8");<br /> $query = mysql_query("SELECT * FROM temp_t where mykey = '1'");<br /> $jon = array();<br /> while($rows = mysql_fetch_array($query)){<br /> $jon['a'] = $rows['id'];<br /> $jon['b'] = $rows['title'];<br /> $jon['c'] = $rows['content'];<br /> $jon['d'] = $rows['date'];<br /> $pjon .= json_encode($jon).',';<br /> }<br /> mysql_close($connextion);<br /> $njso = json_decode('['.substr($pjon,0,-1).']');<br /> foreach($njso as $nx){ <br /> if($nx->a){<br /> require dirname(__FILE__) . '/../connection.php';<br /> mysql_select_db("news",$connextion);<br /> mysql_query("SET NAMES utf8");<br /> mysql_query("UPDATE temp_t SET mykey='2' WHERE id='".mysql_real_escape_string($nx->a)."'");<br /> mysql_query("INSERT INTO table1 (id,title,content,date) values ('".mysql_real_escape_string($nx->a)."','".mysql_real_escape_string($nx->b)."','".mysql_real_escape_string($nx->c)."','".mysql_real_escape_string($nx->d)."'");<br /> mysql_close($connextion);<br /> }<br /> }