mysql 造1亿条记录的单表–大数据表

读写文件背景及木:现有数据1000w单表,为压力测试准备1亿条数据。步骤:1.将1000w条记录,除id外都导入到多个文件中://DELIMITERDROP PROCEDURE if EXISTS createManyTable;create PROCEDURE createManyTable()BEGINDECLARE i int;DECLARE fileName VARCHAR

读写文件

背景及木:现有数据1000w单表,为压力测试准备1亿条数据。

步骤:

1.将1000w条记录,除id外都导入到多个文件中:

i fileName ( i<span style=”color: #0000ff;”>while i<span style=”color: #808080;”><<span style=”color: #800000; font-weight: bold;”>251<span style=”color: #000000;”>
DO
<span style=”color: #0000ff;”>SET fileName<span style=”color: #808080;”>=CONCAT(<span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>flog<span style=”color: #ff0000;”>’,i,<span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>.txt<span style=”color: #ff0000;”>'<span style=”color: #000000;”>);

<span style=”color: #0000ff;”>SET <span style=”color: #008000;”>@STMT :<span style=”color: #808080;”>=CONCAT(“<span style=”color: #0000ff;”>select<span style=”color: #000000;”> xx,xx,…. <span style=”color: #0000ff;”>into outfile <span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>temp/”,fileName,”<span style=”color: #ff0000;”>’ lines terminated <span style=”color: #0000ff;”>by <span style=”color: #ff0000;”>'<span style=”color: #ff0000;”>\r\n<span style=”color: #ff0000;”>’ <span style=”color: #0000ff;”>from f_log <span style=”color: #0000ff;”>WHERE id<span style=”color: #808080;”>>= “,<span style=”color: #800000; font-weight: bold;”>40000<span style=”color: #808080;”>(i<span style=”color: #808080;”>-<span style=”color: #800000; font-weight: bold;”>1),” <span style=”color: #808080;”>AND id<span style=”color: #808080;”>< “,<span style=”color: #800000; font-weight: bold;”>40000<span style=”color: #808080;”><span style=”color: #000000;”>i);

<span style=”color: #0000ff;”>PREPARE STMT <span style=”color: #0000ff;”>FROM <span style=”color: #008000;”>@STMT<span style=”color: #000000;”>;
<span style=”color: #0000ff;”>EXECUTE<span style=”color: #000000;”> STMT;

<span style=”color: #0000ff;”>set i<span style=”color: #808080;”>=i<span style=”color: #808080;”>+<span style=”color: #800000; font-weight: bold;”>1<span style=”color: #000000;”>;
<span style=”color: #0000ff;”>end <span style=”color: #0000ff;”>while<span style=”color: #000000;”>;
<span style=”color: #0000ff;”>END<span style=”color: #000000;”>;
<span style=”color: #808080;”>//<span style=”color: #000000;”>DELIMITER
CALL createManyTable();

2. 将上述多个文件合并到同一个文件,并且在第一列加入id列:

main(String[] args) i=10000000 step=40000= File(“E:/data/f_log_data.txt”( k=1;k<251;k++= File("E:/data/temp/f_log_"+k+".txt"==readFile(file,i+step*}

</span><span style=”color: #0000ff;”&gt;public</span> <span style=”color: #0000ff;”&gt;static</span> StringBuffer readFile(File file,<span style=”color: #0000ff;”&gt;int</span> start) <span style=”color: #0000ff;”&gt;throws</span><span style=”color: #000000;”&gt; IOException{
StringBuffer sb</span>=<span style=”color: #0000ff;”&gt;new</span><span style=”color: #000000;”&gt; StringBuffer();
BufferedReader reader</span>=<span style=”color: #0000ff;”&gt;new</span> BufferedReader(<span style=”color: #0000ff;”&gt;new</span><span style=”color: #000000;”&gt; FileReader(file));
String line</span>=””<span style=”color: #000000;”&gt;;

</span><span style=”color: #0000ff;”&gt;while</span>(line != <span style=”color: #0000ff;”&gt;null</span><span style=”color: #000000;”&gt;){
line </span>=<span style=”color: #000000;”&gt; reader.readLine();
</span><span style=”color: #0000ff;”&gt;if</span>(line == <span style=”color: #0000ff;”&gt;null</span><span style=”color: #000000;”&gt;){
</span><span style=”color: #0000ff;”&gt;break</span><span style=”color: #000000;”&gt;;
}
</span><span style=”color: #0000ff;”&gt;if</span>(line.trim().equalsIgnoreCase(“”<span style=”color: #000000;”&gt;)){
</span><span style=”color: #0000ff;”&gt;continue</span><span style=”color: #000000;”&gt;;
}
start</span>++<span style=”color: #000000;”&gt;;
sb.append(start</span>+”\t”+line.trim()+”\r\n”<span style=”color: #000000;”&gt;);
}
reader.close();
</span><span style=”color: #0000ff;”&gt;return</span><span style=”color: #000000;”&gt; sb;
}

</span><span style=”color: #0000ff;”&gt;public</span> <span style=”color: #0000ff;”&gt;static</span> <span style=”color: #0000ff;”&gt;void</span> writeFile(File file,StringBuffer sb) <span style=”color: #0000ff;”&gt;throws</span><span style=”color: #000000;”&gt; IOException{
BufferedWriter writer </span>= <span style=”color: #0000ff;”&gt;new</span> BufferedWriter(<span style=”color: #0000ff;”&gt;new</span> FileWriter(file,<span style=”color: #0000ff;”&gt;true</span><span style=”color: #000000;”&gt;));
writer.write(sb.toString());
writer.close();
}

</span><span style=”color: #0000ff;”&gt;public</span> <span style=”color: #0000ff;”&gt;void</span> writeFile11() <span style=”color: #0000ff;”&gt;throws</span><span style=”color: #000000;”&gt; IOException{

</span><span style=”color: #008000;”&gt;//</span><span style=”color: #008000;”&gt; TODO Auto-generated method stub</span>
BufferedWriter writer = <span style=”color: #0000ff;”&gt;new</span> BufferedWriter(<span style=”color: #0000ff;”&gt;new</span> FileWriter(<span style=”color: #0000ff;”&gt;new</span> File(“D:/driver/data.txt”),<span style=”color: #0000ff;”&gt;true</span><span style=”color: #000000;”&gt;));
</span><span style=”color: #0000ff;”&gt;for</span>(<span style=”color: #0000ff;”&gt;int</span> i=0;i<1000000;i++<span style=”color: #000000;”&gt;){
</span><span style=”color: #0000ff;”&gt;if</span>(i%10==0<span style=”color: #000000;”&gt;){
writer.write(</span>”赵”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}</span><span style=”color: #0000ff;”&gt;if</span>(i%10==1<span style=”color: #000000;”&gt;){
writer.write(</span>”钱”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}
</span><span style=”color: #0000ff;”&gt;if</span>(i%10==2<span style=”color: #000000;”&gt;){
writer.write(</span>”孙”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}</span><span style=”color: #0000ff;”&gt;if</span>(i%10==3<span style=”color: #000000;”&gt;){
writer.write(</span>”李”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}
</span><span style=”color: #0000ff;”&gt;if</span>(i%10==4<span style=”color: #000000;”&gt;){
writer.write(</span>”郑”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}</span><span style=”color: #0000ff;”&gt;if</span>(i%10==5<span style=”color: #000000;”&gt;){
writer.write(</span>”吴”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}
</span><span style=”color: #0000ff;”&gt;if</span>(i%10==6<span style=”color: #000000;”&gt;){
writer.write(</span>”周”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}</span><span style=”color: #0000ff;”&gt;if</span>(i%10==7<span style=”color: #000000;”&gt;){
writer.write(</span>”王”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}
</span><span style=”color: #0000ff;”&gt;if</span>(i%10==8<span style=”color: #000000;”&gt;){
writer.write(</span>”张”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}</span><span style=”color: #0000ff;”&gt;if</span>(i%10==9<span style=”color: #000000;”&gt;){
writer.write(</span>”刘”+(i/10)+”\t”+ (<span style=”color: #0000ff;”&gt;int</span>)(Math.random()*100)+”\n”<span style=”color: #000000;”&gt;);
}
}
writer.close();

}</span></pre>

3. 将合并后的文件导入到数据表中:

data local infile

`xx`,.........................

注意事项:开始考虑使用存储过程来逐步导入到数据表中,但load data命令不能在存储过程中使用。

  另外,数据的合并也可以以shell脚本完成,但习惯使用java了,因此以java来完成,显得比较复杂。不过,可以随便复习一下java的读写文件,有算不错的经历。

Q&A

时间问题:生成1亿条数据(在有索引的情况下),用时3个小时。如果使用insert语句,估计会疯掉!

作者: dawei

【声明】:永州站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

为您推荐

联系我们

联系我们

0577-28828765

在线咨询: QQ交谈

邮箱: xwei067@foxmail.com

工作时间:周一至周五,9:00-17:30,节假日休息

返回顶部