当前位置: 首页>数据库>正文

提升写入mysql 速度 mysql写入速度实际多少

  本文主要测试mysql插入数据效率,测试机器硬件为:inter i3 (2.13GHz,4核) + 4G内存(2.99G可用) +32位操作系统

一:表结构



CREATE TABLE `record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `openid` varchar(63) NOT NULL,
  `tag_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



 

二:单线程单条插入



public class TestOneByOneController {

    private static Logger logger = LoggerFactory.getLogger(TestOneByOneController.class);

    @Autowired
    private RecordRepository recordRepository;

    private static  final int total = 10000;

    @RequestMapping(value = "/testOneByOne")
    @ResponseBody
    public String testOneByOne(){
        long startTime = System.currentTimeMillis();
        try {
            int i;
            for (i = 0; i < total; i++) {
                String openid = UUID.randomUUID().toString();
                RecordEntity record = new RecordEntity();
                record.setOpenid(openid);
                record.setTagId(i);
                recordRepository.save(record);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }
        Long endTime = System.currentTimeMillis();
        String result =  "testOneByOne spend time is " + (endTime - startTime) + "ms";
        logger.info(result);
        return  result;
    }

}



  1万条数据插入结果:大约需要407s

提升写入mysql 速度 mysql写入速度实际多少,提升写入mysql 速度 mysql写入速度实际多少_java,第1张

 

三:单线程批量插入

(1)测试代码



package com.ws.learn.controller;


@RestController
public class TestController {

    private static Logger logger = LoggerFactory.getLogger(TestController.class);

    private EntityManagerFactory emf;

    @PersistenceUnit//使用这个标记来注入EntityManagerFactory
    public void setEntityManagerFactory(EntityManagerFactory emf) {
        this.emf = emf;
    }

    private static  final int total = 1000000;
    private static final int perThreadDealNum = 10000;


    @RequestMapping(value = "/test")
    @ResponseBody
    public String test(){
        long startTime = System.currentTimeMillis();
        try {
            StringBuilder sb = new StringBuilder(204800);
            sb.append("insert into record(openid,tag_id) values");
            int num = 0;
            for (num = 0; num < total; num++) {
                String openid = UUID.randomUUID().toString();
                sb.append("('" + openid + "'," + num + "),");
                if((num + 1)%perThreadDealNum == 0){
                    sb.deleteCharAt(sb.length() -1);
                    myBatchInsert(sb);
                    sb = new StringBuilder(204800);
                    sb.append("insert into record(openid,tag_id) values");
                }
            }
            if ( num % perThreadDealNum != 0) {
                sb.deleteCharAt(sb.length() -1);
                myBatchInsert(sb);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {

        }
        Long endTime = System.currentTimeMillis();
        String result =  "test spend time is " + (endTime - startTime) + "ms";
        logger.info(result);
        return  result;
    }

    public void myBatchInsert(StringBuilder sb){
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        Query query = em.createNativeQuery(sb.toString());
        query.executeUpdate();

        em.getTransaction().commit();
        em.close();
    }
    
}



(2)测试结果

(2.1)100万的数据,每次批量插入1万,插入时间需要44s左右

2018-04-05 14:21:40.917  INFO 3012 --- [nio-8080-exec-1] com.ws.learn.controller.TestController   : test spend time is 44025ms

 

(2.2)  100万的数据,每次批量插入2万,插入时间需要42s左右,基本没多少改进

2018-04-05 14:25:30.911  INFO 3012 --- [nio-8080-exec-1] com.ws.learn.controller.TestController   : test spend time is 41980ms

 

(2.3)当每次批量插入5万时报错:

提升写入mysql 速度 mysql写入速度实际多少,提升写入mysql 速度 mysql写入速度实际多少_数据库_02,第2张

 

 四:多线程批量插入

 (1)测试代码



@RestController
public class TestThreadController {

    private static Logger logger = LoggerFactory.getLogger(TestThreadController.class);

    private EntityManagerFactory emf;

    @PersistenceUnit//使用这个标记来注入EntityManagerFactory
    public void setEntityManagerFactory(EntityManagerFactory emf) {
        this.emf = emf;
    }

    private static  final int total = 1000000;
    private static final int perThreadDealNum = 10000;


    @RequestMapping(value = "/testWithThread")
    @ResponseBody
    public String testWithThread(){
       long startTime = System.currentTimeMillis();
       try {
           logger.info("" + Runtime.getRuntime().availableProcessors());
           final ExecutorService fixExecutorPool = Executors.newFixedThreadPool(10);

           StringBuilder sb = new StringBuilder(102400);
           sb.append("insert into record(openid,tag_id) values");
           int i;
          for (i = 0; i < total; i++) {
              String openid = UUID.randomUUID().toString();
              sb.append("('" + openid + "'," + i + "),");
              if((i+1)%perThreadDealNum ==0){
                  sb.deleteCharAt(sb.length() -1);
                  fixExecutorPool.execute(new RecordThread(sb, emf));
                  sb = new StringBuilder(204800);
                  sb.append("insert into record(openid,tag_id) values");
              }
           }
           if (i% perThreadDealNum != 0) {
               sb.deleteCharAt(sb.length() -1);
               fixExecutorPool.execute(new RecordThread(sb, emf));
           }
           fixExecutorPool.shutdown();
            while (!fixExecutorPool.awaitTermination(500, TimeUnit.MILLISECONDS)){

            }
       }catch (Exception ex){
           ex.printStackTrace();
       }finally {

       }
       Long endTime = System.currentTimeMillis();
       String result =  "testWithThread spend time is " + (endTime - startTime) + "ms";
       logger.info(result);
       return  result;
    }

}



 



public class RecordThread implements Runnable {
    private StringBuilder sb;
    private EntityManagerFactory emf;
    
    public RecordThread(StringBuilder sb,  EntityManagerFactory emf){
        this.sb = sb;
        this.emf = emf;
    }
    @Override
    public void run() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        Query query = em.createNativeQuery(sb.toString());
        query.executeUpdate();

        em.getTransaction().commit();
        em.close();
    }
}



 

(2)测试结果

 (2.1) 100万的数据,10个固定线程,每次批量插入1万,插入时间需要30339ms左右,比单线程的40+s有一定提升。

提升写入mysql 速度 mysql写入速度实际多少,提升写入mysql 速度 mysql写入速度实际多少_java_03,第3张

第二次运行有25866ms,第三次27187ms

 

(2.2)100万的数据,10个固定线程,每次批量插入5千,插入时间需要29013ms,提升效果不大

提升写入mysql 速度 mysql写入速度实际多少,提升写入mysql 速度 mysql写入速度实际多少_批量插入_04,第4张

 

(2.3)100万的数据,10个固定线程,每次批量插入2万,插入时间需要32511ms左右,时间反而增加了

  注意,如果jvm参数设置过小,此时有可能会出现 Exception in thread "pool-13-thread-1" java.lang.OutOfMemoryError: Java heap space  的异常

我的VM配置:

提升写入mysql 速度 mysql写入速度实际多少,提升写入mysql 速度 mysql写入速度实际多少_提升写入mysql 速度_05,第5张

运行结果:

 

提升写入mysql 速度 mysql写入速度实际多少,提升写入mysql 速度 mysql写入速度实际多少_数据库_06,第6张

 

(2.4)200万的数据,10个固定线程,每次批量插入1万,插入时间需要63084ms

  注意,需要调整好jvm参数,不然会堆内存溢出。

 

(2.5)200万的数据,15个固定线程,每次批量插入1万,插入时间需要62322ms,这种情况下增加线程基本无影响

 

(3)其它测试结果(由于有误差和垃圾回收等影响,数据量越大误差越大,下面为大致结果)

数据量

线程数

单次批量插入

耗时

10万

单线程

2000

5145ms

10万

单线程

5000

4112ms

10万

单线程

10000

4746ms

10万

5

2000

2371ms

10万

5

5000

2074ms

10万

5

10000

2006ms

 

 

 

 

100万

10

10000

25866ms

100万

5

10000

25003ms

100万

5

5000

29883ms

100万

5

2000

35976ms

100万

单线程

10000

40690ms

100万

单线程

5000

45985ms

100万

单线程

2000

57116ms

在实际情况中,需要根据插入数据量大小和任务执行大致所需时间,合理选择线程数和单次批量插入条数。比如上面10万级别下,5000是一个比较合理的选择。当达到一定线程数后,增加线程数对耗时基本无太大影响。单次批量插入数有一定影响。

(4)下面是使用jconsole工具监控的200万数据测试时内存变化,突增的时候是在测试的时候。

提升写入mysql 速度 mysql写入速度实际多少,提升写入mysql 速度 mysql写入速度实际多少_java_07,第7张


https://www.xamrdz.com/database/6gv1937357.html

相关文章: