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 = Calendar.getInstance();
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.append(birthday.get(Calendar.YEAR));
long month = birthday.get(Calendar.MONTH) + 1;
if (month < 10) {
builder.append("0");
}
builder.append(month);
long date = birthday.get(Calendar.DATE);
if (date < 10) {
builder.append("0");
}
builder.append(date);
return builder.toString();
}

 


模拟产生姓名:

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.abs(random.nextInt(72)));
random=new Random();
//位码,0xA0打头,范围第1~94列
lowPos = 161 + Math.abs(random.nextInt(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 index=random.nextInt(Surname.length-1);
//获得一个随机的姓氏
name = Surname[index] +str;

} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return name;
}

 

模拟产生创建时间:

public static Date getTime() {
Random rand = new Random();
Calendar cal = Calendar.getInstance();
cal.set(1900, 0, 1);
long start = cal.getTimeInMillis();
cal.set(2020, 0, 1);
long end = cal.getTimeInMillis();
Date d = new Date(start + (long)(rand.nextDouble() * (end - start)));
return d;
}

 

优化点:

1:尽量使用MySQL自增ID,InnoDB引擎表是基于B+树的索引组织表,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点),如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

2:取消所有的索引,尤其是唯一索引。(同上)每当有一条新的记录插入时,MySQL会根据其插入适当的节点和位置,会导致移动数据,造成大量碎片

3:批量插入可以使SQL日志量(MySQL的binlog和innodb的事务让日志)减少了,降低日志刷盘的数据量和频率,从而提高效率。通过批量插入减少SQL语句解析的次数,减少网络传输的IO。

4:使用事务可以提高数据的插入效率,这是因为进行一个INSERT操作时,MySQL内部会建立一个事务,在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗,所有插入都在执行后才进行提交操作。

5:多线程处理,这个就不要多说了。

 

 

posted @ 2020-04-19 15:29  摘星族  阅读(695)  评论(0编辑  收藏