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

概述读写文件背景及木：现有数据1000w单表，为压力测试准备1亿条数据。步骤：1.将1000w条记录，除id外都导入到多个文件中：//DELIMITERDROP PROCEDURE if EXISTS createManyTable;create PROCEDURE createManyTable()BEGINDECLARE i int;DECLARE fileName VARCHAR(30);set i=1;while i<251DOSET fileName=CONCAT('f_log_',i,'.txt');SET @STMT :=CONCAT("select `xx`,`xx`,`xx`,`xx`,.... into outfile 'temp/",fileName,"' lines terminated by 'rn' from `f_log` WHERE id>= ",40000*(i-1)," AND id< ",40000*i);PREPARE STMT FROM @STMT;EXECUTE STMT;set i=i+1;end while;END;//DELIMITERCALL createManyTable(); 2. 将上述多个文件合并到同一个文件，并且在第一列加入id列：public static void main(String[] args) throws IOException {int i=10000000;int step=40000;File out=new File("E:/data/f_log_data.txt");for(int k=1;k<251;k++){File file=new File("E:/data/temp/f_log_"+k+".txt");StringBuffer sb=new StringBuffer();if(file.exists()){sb=readFile(file,i+step*k);writeFile(out,sb);}}}public static StringBuffer readFile(File file,int start) throws IOException{StringBuffer sb=new StringBuffer();BufferedReader reader=new BufferedReader(new FileReader(file));String line="";while(line != null){line = reader.readLine();if(line == null){break;}if(line.trim().equalsIgnoreCase("")){continue;}start++;sb.append(start+"t"+line.trim()+"rn");}reader.close();return sb;}public static void writeFile(File file,StringBuffer sb) throws IOException{BufferedWriter writer = new BufferedWriter(new FileWriter(file, true));writer.write(sb.toString());writer.close();}public void writeFile11() throws IOException{// TODO Auto-generated method stubBufferedWriter writer = new BufferedWriter(new FileWriter(new File("D:/driver/data.txt"), true));for(int i=0;i<1000000;i++){if(i%10==0){writer.write("赵"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==1){writer.write("钱"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==2){writer.write("孙"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==3){writer.write("李"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==4){writer.write("郑"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==5){writer.write("吴"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==6){writer.write("周"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==7){writer.write("王"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==8){writer.write("张"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}if(i%10==9){writer.write("刘"+(i/10)+"t"+ (int)(Math.random()*100)+"n");}}writer.close();}3. 将合并后的文件导入到数据表中：load data local infile '/tmp/finance_log_data.txt' into table f_log(`id`,`xx`,`xx`,.........................);注意事项：开始考虑使用存储过程来逐步导入到数据表中，但load data命令不能在存储过程中使用。另外，数据的合并也可以以shell脚本完成，但习惯使用java了，因此以java来完成，显得比较复杂。不过，可以随便复习一下java的读写文件，有算不错的经历。Q&A时间问题：生成1亿条数据(在有索引的情况下)，用时3个小时。如果使用insert语句，估计会疯掉！

读写文件

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

步骤：

1.将1000w条记录，除ID外都导入到多个文件中：

i filename ( iwhile i<251
DO
SET filename=CONCAT('flog',i,'.txt');

SET @STMT :=CONCAT("select xx,xx,.... into outfile 'temp/",filename,"' lines terminated by '\r\n' from f_log WHERE ID>= ",40000(i-1)," AND ID< ",40000i);

PREPARE STMT FROM @STMT;
EXECUTE STMT;

set i=i+1;
end while;
END;
//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 https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;static</span> StringBuffer read<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>(<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a> <a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span> start) <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;throws</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; IOException{    StringBuffer sb</span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; StringBuffer();    BufferedReader reader</span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span> BufferedReader(<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; <a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>Reader(<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>));    String <a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne</span>=""<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;while</span>(<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne != <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;n<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>l</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){        <a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; reader.read<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne();        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne == <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;n<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>l</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;break</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;        }        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne.trim().equalsIg<a href="https://www.jb51.cc/tag/nor/" target="_blank" >nor</a>eCase(""<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;)){            </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;continue</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;        }        start</span>++<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;        sb.append(start</span>+"\t"+<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne.trim()+"\r\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);    }    reader.close();    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; sb;}</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;static</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;vo<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a></span> write<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>(<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a> <a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>,StringBuffer sb) <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;throws</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; IOException{    Buffere<a href="https://m.jb51.cc/tag/DW/" target="_blank" >DW</a>riter writer </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span> Buffere<a href="https://m.jb51.cc/tag/DW/" target="_blank" >DW</a>riter(<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span> <a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>Writer(<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;true</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;));    writer.write(sb.toString());    writer.close();}</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;vo<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a></span> write<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>11() <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;throws</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; IOException{    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000;"&gt;//</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000;"&gt; T<a href="https://www.jb51.cc/tag/odo/" target="_blank" >odo</a> <a href="https://m.jb51.cc/tag/auto/" target="_blank" >auto</a>-generated method stub</span>    Buffere<a href="https://m.jb51.cc/tag/DW/" target="_blank" >DW</a>riter writer = <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span> Buffere<a href="https://m.jb51.cc/tag/DW/" target="_blank" >DW</a>riter(<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span> <a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>Writer(<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span> <a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>("D:/driver/data.txt"),<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;true</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;));    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;for</span>(<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span> i=0;i<1000000;i++<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==0<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"赵"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==1<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"钱"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==2<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"孙"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==3<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"李"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==4<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"郑"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==5<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"吴"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==6<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"周"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==7<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"王"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }        </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==8<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"张"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;if</span>(i%10==9<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;){            writer.write(</span>"刘"+(i/10)+"\t"+ (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;int</span>)(Math.random()*100)+"\n"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);        }    }    writer.close();}</span></pre>

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

data local infile