MySQL千万级数据进⾏插⼊，基础数据3千万，插⼊1千万数据时间约为4.7分钟,10个线程同时插⼊
测试MySQL千万条数据插⼊速度
使⽤多线程，每条线程处理数据100万条，每次插⼊携带数据7万条进⾏提交
数据库基数为0，插⼊1000万条数据，时间为311957毫秒，也就是311.957秒，约为5.2分钟
数据库基础数据为2000万条数据，插⼊1000万条数据，时间为302545毫秒，也就是502.545秒。约5.1分钟
数据库基础数据为3000万条数据，插⼊1000万条数据，时间为286377毫秒，也就是286.377秒。约4.7分
1000万条数据⽇志
===================>>>>>DefaultManagedAwareThreadFactory-1
===================>>>>>DefaultManagedAwareThreadFactory-2
===================>>>>>DefaultManagedAwareThreadFactory-3
===================>>>>>DefaultManagedAwareThreadFactory-4
===================>>>>>DefaultManagedAwareThreadFactory-5
===================>>>>>DefaultManagedAwareThreadFactory-6
===================>>>>>DefaultManagedAwareThreadFactory-7
===================>>>>>DefaultManagedAwareThreadFactory-8
===================>>>>>DefaultManagedAwareThreadFactory-9
===================>>>>>DefaultManagedAwareThreadFactory-10
DefaultManagedAwareThreadFactory-2======结束=====>>>>285025
DefaultManagedAwareThreadFactory-7======结束=====>>>>286669
DefaultManagedAwareThreadFactory-3======结束=====>>>>296607
DefaultManagedAwareThreadFactory-6======结束=====>>>>298840
DefaultManagedAwareThreadFactory-10======结束=====>>>>296657
DefaultManagedAwareThreadFactory-4======结束=====>>>>301761
DefaultManagedAwareThreadFactory-5======结束=====>>>>302579
DefaultManagedAwareThreadFactory-8======结束=====>>>>301438
DefaultManagedAwareThreadFactory-1======结束=====>>>>311957
DefaultManagedAwareThreadFactory-9======结束=====>>>>304187
基础数据2000万，插⼊1000万条数据⽇志：
===================>>>>>DefaultManagedAwareThreadFactory-1
===================>>>>>DefaultManagedAwareThreadFactory-2
===================>>>>>DefaultManagedAwareThreadFactory-3
===================>>>>>DefaultManagedAwareThreadFactory-4
===================>>>>>DefaultManagedAwareThreadFactory-5
===================>>>>>DefaultManagedAwareThreadFactory-6
===================>>>>>DefaultManagedAwareThreadFactory-7
===================>>>>>DefaultManagedAwareThreadFactory-8
===================>>>>>DefaultManagedAwareThreadFactory-9
===================>>>>>DefaultManagedAwareThreadFactory-10
DefaultManagedAwareThreadFactory-8======结束=====>>>>276787
DefaultManagedAwareThreadFactory-3======结束=====>>>>284162
DefaultManagedAwareThreadFactory-4======结束=====>>>>284252
DefaultManagedAwareThreadFactory-2======结束=====>>>>291498
DefaultManagedAwareThreadFactory-1======结束=====>>>>297639
DefaultManagedAwareThreadFactory-7======结束=====>>>>292803
DefaultManagedAwareThreadFactory-5======结束=====>>>>297715
DefaultManagedAwareThreadFactory-9======结束=====>>>>297572
DefaultManagedAwareThreadFactory-10======结束=====>>>>296322 DefaultManagedAwareThreadFactory-6======结束=====>>>>302545
基础数据3000万，插⼊1000万条数据⽇志：
===================>>>>>DefaultManagedAwareThreadFactory-1
===================>>>>>DefaultManagedAwareThreadFactory-2
===================>>>>>DefaultManagedAwareThreadFactory-3
===================>>>>>DefaultManagedAwareThreadFactory-4
===================>>>>>DefaultManagedAwareThreadFactory-5
===================>>>>>DefaultManagedAwareThreadFactory-6
===================>>>>>DefaultManagedAwareThreadFactory-7
===================>>>>>DefaultManagedAwareThreadFactory-8
===================>>>>>DefaultManagedAwareThreadFactory-9
===================>>>>>DefaultManagedAwareThreadFactory-10 DefaultManagedAwareThreadFactory-6======结束=====>>>>259247
DefaultManagedAwareThreadFactory-2======结束=====>>>>264036
DefaultManagedAwareThreadFactory-3======结束=====>>>>265275
DefaultManagedAwareThreadFactory-7======结束=====>>>>264781
DefaultManagedAwareThreadFactory-10======结束=====>>>>265781 DefaultManagedAwareThreadFactory-9======结束=====>>>>271145
DefaultManagedAwareThreadFactory-5======结束=====>>>>281170
DefaultManagedAwareThreadFactory-1======结束=====>>>>286377
DefaultManagedAwareThreadFactory-4======结束=====>>>>283481
DefaultManagedAwareThreadFactory-8======结束=====>>>>279556
模拟数据测试：
模拟随机产⽣⼿机号码：
/**
* 返回⼿机号码
*/
private static String[] telFirst = "134,135,136,137,138,139,150,151,152,157,158,159,130,131,132,155,156,133,153".split(","); public static int getNum(int start, int end) {
return (int) (Math.random() * (end - start + 1) + start);
}
public static String getTel() {
int index = getNum(0, telFirst.length - 1);
String first = telFirst[index];
String second = String.valueOf(getNum(1, 888) + 10000).substring(1);
String third = String.valueOf(getNum(1, 9100) + 10000).substring(1);
return first + second + third;
}
模拟随机产⽣出⽣⽇期：
/**
* 随机出⽣⽇期
*
* @return
*/
public static String randomBirthday() {
Calendar birthday = Instance();
birthday.set(Calendar.YEAR, (int) (Math.random() * 60) + 1950);
birthday.set(Calendar.MONTH, (int) (Math.random() * 12));
birthday.set(Calendar.DATE, (int) (Math.random() * 31));
StringBuilder builder = new StringBuilder();
builder.(Calendar.YEAR));
long month = (Calendar.MONTH) + 1;
if (month < 10) {
builder.append("0");
}
builder.append(month);
long date = (Calendar.DATE);
if (date < 10) {
builder.append("0");
}
builder.append(date);
String();
}
模拟产⽣姓名：
private static final String[] Surname= {"赵","钱","孙","李","周","吴","郑","王","冯","陈","褚","卫","蒋","沈","韩","杨","朱","秦","尤","许", "何","吕","施","张","孔","曹","严","华","⾦","魏","陶","姜","戚","谢","邹","喻","柏","⽔","窦","章","云","苏","潘","葛","奚","范","彭","郎", "鲁","韦","昌","马","苗","凤","花","⽅","俞","任","袁","柳","酆","鲍","史","唐","费","廉","岑","薛","雷","贺","倪","汤","滕","殷", "罗","毕","郝","邬","安","常","乐","于","时","傅","⽪","卞","齐","康","伍","余","元","⼘","顾","孟","平","黄","和", "穆","萧","尹","姚","邵","湛","汪","祁","⽑","禹","狄","⽶","贝","明","臧","计","伏","成","戴","谈","宋","茅","庞","熊","纪","舒", "屈","项","祝","董","梁","杜","阮","蓝","闵","席","季","⿇","强","贾","路","娄","危","江","童","颜","郭","梅","盛","林","刁","钟", "徐","邱","骆","⾼","夏","蔡","⽥","樊","胡","凌","霍","虞","万","⽀","柯","昝","管","卢","莫","经","房","裘","缪","⼲","解","应", "宗","丁","宣","贲","邓","郁","单","杭","洪","包","诸","左","⽯","崔","吉","钮","龚","程","嵇","邢","滑","裴","陆","荣","翁","荀", "⽺","于","惠","甄","曲","家","封","芮","羿","储","靳","汲","邴","糜","松","井","段","富","巫","乌","焦","巴","⼸","牧","隗","⼭", "⾕","车","侯","宓","蓬","全","郗","班","仰","秋","仲","伊","宫","宁","仇","栾","暴","⽢","钭","厉","戎","祖","武","符","刘","景", "詹","束","龙","叶","幸","司","韶","郜","黎","蓟","溥","印","宿","⽩","怀","蒲","邰","从","鄂","索","咸","籍","赖","卓","蔺","屠", "蒙","池","乔","阴","郁","胥","能","苍","双","闻","莘","党","翟","谭","贡","劳","逄","姬","申","扶","堵","冉","宰","郦","雍","却", "璩","桑","桂","濮","⽜","寿","通","边","扈","燕","冀","浦","尚","农","温","别","庄","晏","柴","瞿","阎","充","慕","连","茹","习", "宦","艾","鱼","容","向","古","易","慎","⼽","廖","庾","终","暨","居","衡","步","都","耿","满","弘","匡","国","⽂","寇","⼴","禄", "阙","东","欧","⽎","沃","利","蔚","越","夔","隆","师","巩","厍","聂","晁","勾","敖","融","冷","訾","⾟","阚","那","简","饶","空", "曾","⽏","沙","乜","养","鞠","须","丰","巢","关","蒯","相","查","后","荆","红","游","郏","竺","权","逯","盖","益","桓","公","仉",
"督","岳","帅","缑","亢","况","郈","有","琴","归","海","晋","楚","闫","法","汝","鄢","涂","钦","商","牟","佘","佴","伯","赏","墨", "哈","谯","篁","年","爱","阳","佟","⾔","福","南","⽕","铁","迟","漆","官","冼","真","展","繁","檀","祭","密","敬","揭","舜","楼", "疏","冒","浑","挚","胶","随","⾼","皋","原","种","练","弥","仓","眭","蹇","覃","阿","门","恽","来","綦","召","仪","风","介","巨", "⽊","京","狐","郇","虎","枚","抗","达","杞","苌","折","麦","庆","过","⽵","端","鲜","皇","亓","⽼","是","秘","畅","邝","还","宾", "闾","辜","纵","侴","万俟","司马","上官","欧阳","夏侯","诸葛","闻⼈","东⽅","赫连","皇甫","⽺⾆","尉迟","公⽺","澹台","公冶","宗正", "濮阳","淳于","单于","太叔","申屠","公孙","仲孙","轩辕","令狐","钟离","宇⽂","长孙","慕容","鲜于","闾丘","司徒","司空","兀官","司寇", "南门","呼延","⼦车","颛孙","端⽊","巫马","公西","漆雕","车正","壤驷","公良","拓跋","夹⾕","宰⽗","⾕梁","段⼲","百⾥","东郭","微⽣", "梁丘","左丘","东门","西门","南宫","第五","公仪","公乘","太史","仲长","叔孙","屈突","尔朱","东乡","相⾥","胡母","司城","张廖","雍门", "⽏丘","贺兰","綦⽏","屋庐","独孤","南郭","北宫","王孙"};
public static String getChineseName() {
String str = null;
String name = null;
int highPos, lowPos;
Random random = new Random();
//区码，0xA0打头，从第16区开始，即0xB0=11*16=176,16~55⼀级汉字，56~87⼆级汉字
highPos = (176 + Math.Int(72)));
random=new Random();
//位码，0xA0打头，范围第1~94列
lowPos = 161 + Math.Int(94));
byte[] bArr = new byte[2];
bArr[0] = (new Integer(highPos)).byteValue();
bArr[1] = (new Integer(lowPos)).byteValue();
try {
//区位码组合成汉字
str = new String(bArr, "GB2312");
漆雕
int Int(Surname.length-1);
//获得⼀个随机的姓⽒
name = Surname[index] +str;
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return name;
}
模拟产⽣创建时间：
public static Date getTime() {
Random rand = new Random();
Calendar cal = Instance();
cal.set(1900, 0, 1);
long start = TimeInMillis();
cal.set(2020, 0, 1);
long end = TimeInMillis();
Date d = new Date(start + (long)(Double() * (end - start)));
return d;
}
优化点：
1：尽量使⽤MySQL⾃增ID，InnoDB引擎表是基于B+树的索引组织表，数据记录本⾝被存于主索引（⼀颗B+Tree）的叶⼦节点上。这就要求同⼀个叶⼦节点内（⼤⼩为⼀个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有⼀条新的记录插⼊时，MySQL会根据其主键将其插⼊适当的节点
和位置，如果页⾯达到装载因⼦（InnoDB默认为15/16），则开辟⼀个新的页（节点），如果表使⽤⾃增主键，那么每次插⼊新的记录，记录就会顺序添加到当前索引节点的后续位置，当⼀页写满，就会⾃动开辟⼀个新的页。如果使⽤⾮⾃增主键（如果⾝份证号或学号等），由于每次插⼊主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置⽽移动数据，甚⾄⽬标页⾯可能已经被回写到磁盘上⽽从缓存中清掉，此时⼜要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了⼤量的碎⽚，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页⾯。
2：取消所有的索引，尤其是唯⼀索引。（同上）每当有⼀条新的记录插⼊时，MySQL会根据其插⼊适当的节点和位置，会导致移动数据，造成⼤量碎⽚
4：使⽤事务可以提⾼数据的插⼊效率，这是因为进⾏⼀个INSERT操作时，MySQL内部会建⽴⼀个事务，在事务内才进⾏真正插⼊处理操作。通过使⽤事务可以减少创建事务的消耗，所有插⼊都在执⾏后才进⾏提交操作。
5：多线程处理，这个就不要多说了。
发布评论