日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告:魔扣目錄網(wǎng)為廣大站長(zhǎng)提供免費(fèi)收錄網(wǎng)站服務(wù),提交前請(qǐng)做好本站友鏈:【 網(wǎng)站目錄:http://www.ylptlb.cn 】, 免友鏈快審服務(wù)(50元/站),

點(diǎn)擊這里在線咨詢客服
新站提交
  • 網(wǎng)站:51998
  • 待審:31
  • 小程序:12
  • 文章:1030137
  • 會(huì)員:747

10萬條數(shù)據(jù)批量插入,到底怎么做才快?

 

基本上明白了這個(gè)小伙伴的意思,于是我自己也寫了個(gè)測(cè)試案例,重新整理了今天這篇文章,希望和小伙伴們一起探討這個(gè)問題,也歡迎小伙伴們提出更好的方案。

1. 思路分析

批量插入這個(gè)問題,我們用 JDBC 操作,其實(shí)就是兩種思路吧:

  1. 用一個(gè) for 循環(huán),把數(shù)據(jù)一條一條的插入(這種需要開啟批處理)。
  2. 生成一條插入 sql,類似這種 insert into user(username,address) values('aa','bb'),('cc','dd')... 。

到底哪種快呢?

我們從兩方面來考慮這個(gè)問題:

  1. 插入 SQL 本身執(zhí)行的效率。
  2. 網(wǎng)絡(luò) I/O。

先說第一種方案,就是用 for 循環(huán)循環(huán)插入:

  • 這種方案的優(yōu)勢(shì)在于,JDBC 中的 PreparedStatement 有預(yù)編譯功能,預(yù)編譯之后會(huì)緩存起來,后面的 SQL 執(zhí)行會(huì)比較快并且 JDBC 可以開啟批處理,這個(gè)批處理執(zhí)行非常給力。
  • 劣勢(shì)在于,很多時(shí)候我們的 SQL 服務(wù)器和應(yīng)用服務(wù)器可能并不是同一臺(tái),所以必須要考慮網(wǎng)絡(luò) IO,如果網(wǎng)絡(luò) IO 比較費(fèi)時(shí)間的話,那么可能會(huì)拖慢 SQL 執(zhí)行的速度。

再來說第二種方案,就是生成一條 SQL 插入:

  • 這種方案的優(yōu)勢(shì)在于只有一次網(wǎng)絡(luò) IO,即使分片處理也只是數(shù)次網(wǎng)絡(luò) IO,所以這種方案不會(huì)在網(wǎng)絡(luò) IO 上花費(fèi)太多時(shí)間。
  • 當(dāng)然這種方案有好幾個(gè)劣勢(shì),一是 SQL 太長(zhǎng)了,甚至可能需要分片后批量處理;二是無法充分發(fā)揮 PreparedStatement 預(yù)編譯的優(yōu)勢(shì),SQL 要重新解析且無法復(fù)用;三是最終生成的 SQL 太長(zhǎng)了,數(shù)據(jù)庫管理器解析這么長(zhǎng)的 SQL 也需要時(shí)間。

所以我們最終要考慮的就是我們?cè)诰W(wǎng)絡(luò) IO 上花費(fèi)的時(shí)間,是否超過了 SQL 插入的時(shí)間?這是我們要考慮的核心問題。

2. 數(shù)據(jù)測(cè)試

接下來我們來做一個(gè)簡(jiǎn)單的測(cè)試,批量插入 5 萬條數(shù)據(jù)看下。

首先準(zhǔn)備一個(gè)簡(jiǎn)單的測(cè)試表:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

接下來創(chuàng)建一個(gè) Spring Boot 工程,引入 MyBatis 依賴和 MySQL 驅(qū)動(dòng),然后 Application.properties 中配置一下數(shù)據(jù)庫連接信息:

spring.datasource.username=root
spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///batch_insert?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

大家需要注意,這個(gè)數(shù)據(jù)庫連接 URL 地址中多了一個(gè)參數(shù) rewriteBatchedStatements ,這是核心。

MySQL JDBC 驅(qū)動(dòng)在默認(rèn)情況下會(huì)無視 executeBatch() 語句,把我們期望批量執(zhí)行的一組 sql 語句拆散,一條一條地發(fā)給 MySQL 數(shù)據(jù)庫,批量插入實(shí)際上是單條插入,直接造成較低的性能。將 rewriteBatchedStatements 參數(shù)值為 true , 數(shù)據(jù)庫驅(qū)動(dòng)才會(huì)幫我們批量執(zhí)行 SQL 。

OK,這樣準(zhǔn)備工作就做好了。

2.1 方案一測(cè)試

首先我們來看方案一的測(cè)試,即一條一條的插入(實(shí)際上是批處理)。

首先創(chuàng)建相應(yīng)的 mapper,如下:

@Mapper
public interface UserMapper {
    Integer addUserOneByOne(User user);
}

對(duì)應(yīng)的 XML 文件如下:

<insert id="addUserOneByOne">
    insert into user (username,address,password) values (#{username},#{address},#{password})
</insert>

service 如下:

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {
    private static final Logger logger = LoggerFactory.getLogger(UserService.class);
    @Autowired
    UserMapper userMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Transactional(rollbackFor = Exception.class)
    public void addUserOneByOne(List<User> users) {
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        UserMapper um = session.getMapper(UserMapper.class);
        long startTime = System.currentTimeMillis();
        for (User user : users) {
            um.addUserOneByOne(user);
        }
        session.commit();
        long endTime = System.currentTimeMillis();
        logger.info("一條條插入 SQL 耗費(fèi)時(shí)間 {}", (endTime - startTime));
    }
}

這里我要說一下:

雖然是一條一條的插入,但是我們要開啟批處理模式(BATCH),這樣前前后后就只用這一個(gè) SqlSession,如果不采用批處理模式,反反復(fù)復(fù)的獲取 Connection 以及釋放 Connection 會(huì)耗費(fèi)大量時(shí)間,效率奇低,這種效率奇低的方式松哥就不給大家測(cè)試了。

接下來寫一個(gè)簡(jiǎn)單的測(cè)試接口看下:

@RestController
public class HelloController {
    private static final Logger logger = getLogger(HelloController.class);
    @Autowired
    UserService userService;
    /**
     * 一條一條插入
     */
    @GetMapping("/user2")
    public void user2() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 50000; i++) {
            User u = new User();
            u.setAddress("廣州:" + i);
            u.setUsername("張三:" + i);
            u.setPassword("123:" + i);
            users.add(u);
        }
        userService.addUserOneByOne(users);
    }
}

寫個(gè)簡(jiǎn)單的單元測(cè)試:

/**
 * 
 * 單元測(cè)試加事務(wù)的目的是為了插入之后自動(dòng)回滾,避免影響下一次測(cè)試結(jié)果
 * 一條一條插入
 */
@Test
@Transactional
void addUserOneByOne() {
    List<User> users = new ArrayList<>();
    for (int i = 0; i < 50000; i++) {
        User u = new User();
        u.setAddress("廣州:" + i);
        u.setUsername("張三:" + i);
        u.setPassword("123:" + i);
        users.add(u);
    }
    userService.addUserOneByOne(users);
}

可以看到,耗時(shí) 901 毫秒,5w 條數(shù)據(jù)插入不到 1 秒。

2.2 方案二測(cè)試

方案二是生成一條 SQL 然后插入。

mapper 如下:

@Mapper
public interface UserMapper {
    void addByOneSQL(@Param("users") List<User> users);
}

對(duì)應(yīng)的 SQL 如下:

<insert id="addByOneSQL">
    insert into user (username,address,password) values
    <foreach collection="users" item="user" separator=",">
        (#{user.username},#{user.address},#{user.password})
    </foreach>
</insert>

service 如下:

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {
    private static final Logger logger = LoggerFactory.getLogger(UserService.class);
    @Autowired
    UserMapper userMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;
    @Transactional(rollbackFor = Exception.class)
    public void addByOneSQL(List<User> users) {
        long startTime = System.currentTimeMillis();
        userMapper.addByOneSQL(users);
        long endTime = System.currentTimeMillis();
        logger.info("合并成一條 SQL 插入耗費(fèi)時(shí)間 {}", (endTime - startTime));
    }
}

然后在單元測(cè)試中調(diào)一下這個(gè)方法:

/**
 * 合并成一條 SQL 插入
 */
@Test
@Transactional
void addByOneSQL() {
    List<User> users = new ArrayList<>();
    for (int i = 0; i < 50000; i++) {
        User u = new User();
        u.setAddress("廣州:" + i);
        u.setUsername("張三:" + i);
        u.setPassword("123:" + i);
        users.add(u);
    }
    userService.addByOneSQL(users);
}

可以看到插入 5 萬條數(shù)據(jù)耗時(shí) 1805 毫秒。

可以看到,生成一條 SQL 的執(zhí)行效率還是要差一點(diǎn)。

另外還需要注意,第二種方案還有一個(gè)問題,就是當(dāng)數(shù)據(jù)量大的時(shí)候,生成的 SQL 將特別的長(zhǎng),MySQL 可能一次性處理不了這么大的 SQL,這個(gè)時(shí)候就需要修改 MySQL 的配置或者對(duì)待插入的數(shù)據(jù)進(jìn)行分片處理了,這些操作又會(huì)導(dǎo)致插入時(shí)間更長(zhǎng)。

2.3 對(duì)比分析

很明顯,方案一更具優(yōu)勢(shì)。當(dāng)批量插入十萬、二十萬數(shù)據(jù)的時(shí)候,方案一的優(yōu)勢(shì)會(huì)更加明顯(方案二則需要修改 MySQL 配置或者對(duì)待插入數(shù)據(jù)進(jìn)行分片)。

3. MP 怎么做的?

小伙伴們知道,其實(shí) MyBatis Plus 里邊也有一個(gè)批量插入的方法 saveBatch,我們來看看它的實(shí)現(xiàn)源碼:

@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {
    String sqlStatement = getSqlStatement(SqlMethod.INSERT_ONE);
    return executeBatch(entityList, batchSize, (sqlSession, entity) -> sqlSession.insert(sqlStatement, entity));
}

可以看到,這里拿到的 sqlStatement 就是一個(gè) INSERT_ONE ,即一條一條插入。

再來看 executeBatch 方法,如下:

public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
    Assert.isFalse(batchSize < 1, "batchSize must not be less than one");
    return !CollectionUtils.isEmpty(list) && executeBatch(entityClass, log, sqlSession -> {
        int size = list.size();
        int i = 1;
        for (E element : list) {
            consumer.accept(sqlSession, element);
            if ((i % batchSize == 0) || i == size) {
                sqlSession.flushStatements();
            }
            i++;
        }
    });
}

這里注意 return 中的第三個(gè)參數(shù),是一個(gè) lambda 表達(dá)式,這也是 MP 中批量插入的核心邏輯,可以看到,MP 先對(duì)數(shù)據(jù)進(jìn)行分片(默認(rèn)分片大小是 1000),分片完成之后,也是一條一條的插入。繼續(xù)查看 executeBatch 方法,就會(huì)發(fā)現(xiàn)這里的 sqlSession 其實(shí)也是一個(gè)批處理的 sqlSession,并非普通的 sqlSession。

綜上,MP 中的批量插入方案給我們 2.1 小節(jié)的批量插入思路其實(shí)是一樣的。

4. 小結(jié)

好啦,經(jīng)過上面的分析,現(xiàn)在小伙伴們知道了批量插入該怎么做了吧?

感興趣的小伙伴不妨試試~

最后再次感謝 BUG 童鞋提出的意見~

分享到:
標(biāo)簽:數(shù)據(jù)
用戶無頭像

網(wǎng)友整理

注冊(cè)時(shí)間:

網(wǎng)站:5 個(gè)   小程序:0 個(gè)  文章:12 篇

  • 51998

    網(wǎng)站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會(huì)員

趕快注冊(cè)賬號(hào),推廣您的網(wǎng)站吧!
最新入駐小程序

數(shù)獨(dú)大挑戰(zhàn)2018-06-03

數(shù)獨(dú)一種數(shù)學(xué)游戲,玩家需要根據(jù)9

答題星2018-06-03

您可以通過答題星輕松地創(chuàng)建試卷

全階人生考試2018-06-03

各種考試題,題庫,初中,高中,大學(xué)四六

運(yùn)動(dòng)步數(shù)有氧達(dá)人2018-06-03

記錄運(yùn)動(dòng)步數(shù),積累氧氣值。還可偷

每日養(yǎng)生app2018-06-03

每日養(yǎng)生,天天健康

體育訓(xùn)練成績(jī)?cè)u(píng)定2018-06-03

通用課目體育訓(xùn)練成績(jī)?cè)u(píng)定