记录一个mysql大量数据快速插入的方法

最新推荐文章于 2025-09-23 21:36:15 发布

原创最新推荐文章于 2025-09-23 21:36:15 发布 · 2.4w 阅读

35 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #mysql大量数据插入 #java mysql #mysql myisam #mysql插入大量数据性能

javaweb入门学习专栏收录该内容

35 篇文章

订阅专栏

本文介绍了一种通过批量插入数据来提高MySQL(MyISAM引擎)插入性能的方法，并通过实验对比单条插入与批量插入的效率差异。

经过数月的历练与开发，终于在这个月手上负责的一个项目即将交付了，看着这一两个月让我偶感压力的繁杂的事情一件件处理完毕，原本动荡的心静也渐渐平静了些许。此时再回过头过去看那些困扰我N多个日夜的某些问题其实也不过如此。有些事情自己无法改变，只能乐观地坦然地面对。面对充满未知的未来努力让自己保持每一天都快乐才是王道，哈哈！

在本周将一个需要处理上千万数据量的项目部署到UAT环境后，发现数据插入到mysql(mariadb)的时间太慢了，尽管已经根据项目需要将原来的innodb存储引擎修改为了更适合大量数据插入和查询效率更高的myisam引擎。通过对代码再次进行分析后发现是由于代码对于数据插入到mysql的过程中耗费了大量的时间，由于myisam又不支持事务，不能设置批量插入，思量后决定将之前的单条insert语句修改为多条数据的insert语句或许性能就会有所提升，正好趁着今天放假将这块的代码先写个DEMO给测试下。

测试代码

测试代码如下：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.LinkedBlockingQueue;

@RequestMapping(value = "mysql-test")
@RestController
public class MysqlTestController {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @GetMapping(value = "mulTest")
    public String mulTest(Integer size, @RequestParam(defaultValue = "false") boolean sharding) {
        initUserInfoTable();
        BlockingQueue<UserInfo> userInfoQueue = initUserInfoQueue(size);
        return size + " 条记录插入，耗时" + processQueue(userInfoQueue, sharding);
    }

    /**
     * 初始化userInfo表
     */
    private void initUserInfoTable() {
        String userInfoTableDdlStr = "create table if not exists  `userinfo` (\n" +
                "  `userId` int(11) DEFAULT NULL,\n" +
                "  `userName` varchar(255) DEFAULT NULL,\n" +
                "  `gender` varchar(255) DEFAULT NULL,\n" +
                "  `phone` varchar(255) DEFAULT NULL,\n" +
                "  `address` varchar(255) DEFAULT NULL,\n" +
                "  `birthDay` bigint(20) DEFAULT NULL,\n" +
                "  `school` varchar(255) DEFAULT NULL,\n" +
                "  `hobby` varchar(255) DEFAULT NULL,\n" +
                "  KEY `index_userid` (`userId`)\n" +
                ") ENGINE=MyISAM DEFAULT CHARSET=utf8";
        jdbcTemplate.execute(userInfoTableDdlStr);
    }

    private long processQueue(BlockingQueue<UserInfo> userInfoQueue, boolean sharding) {
        int mulSize = 200;
        long beginTime = System.currentTimeMillis();
        try {
            if (sharding) {
                while (!userInfoQueue.isEmpty()) {
                    UserInfo userInfo = userInfoQueue.take();
                    StringBuffer singleInserSql = new StringBuffer();
                    singleInserSql.append("INSERT INTO `userInfo` (`userId`, `userName`, `gender`, `phone`, `address`, `birthDay`, `school`, `hobby`) VALUES (?,?,?,?,?,?,?,?)");
                    jdbcTemplate.update(singleInserSql.toString(), new Object[]{
                            userInfo.getUserId(), userInfo.getUserName(), userInfo.getGender(), userInfo.getPhone(), userInfo.getAddress(), userInfo.getBirthDay().getTime() / 1000, userInfo.getSchool(), userInfo.getHobby()
                    });
                }
            } else {
                String insertSqlStr = genMulFixSqlStr(mulSize);
                List<UserInfo> userInfoList = new ArrayList<>();
                while (!userInfoQueue.isEmpty()) {
                    UserInfo userInfo = userInfoQueue.take();
                    userInfoList.add(userInfo);
                    if (userInfoList.size() == mulSize) {
                        Object[] params = getMulParams(userInfoList, userInfoList.size());
                        jdbcTemplate.update(insertSqlStr, params);
                        userInfoList.clear();
                    }
                }
                //插入剩余的数据
                if (!userInfoList.isEmpty()) {
                    String restInsertSql = genMulFixSqlStr(userInfoList.size());
                    Object[] params = getMulParams(userInfoList, userInfoList.size());
                    jdbcTemplate.update(restInsertSql, params);
                    userInfoList.clear();
                }
            }
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        long endTime = System.currentTimeMillis();
        return endTime - beginTime;
    }

    private Object[] getMulParams(List<UserInfo> userInfoList, int mulSize) {
        Object[] params = new Object[mulSize * 8];
        for (int i = 0; i < mulSize; i++) {
            UserInfo userInfo = userInfoList.get(i);
            params[8 * i] = userInfo.getUserId();
            params[8 * i + 1] = userInfo.getUserName();
            params[8 * i + 2] = userInfo.getGender();
            params[8 * i + 3] = userInfo.getPhone();
            params[8 * i + 4] = userInfo.getAddress();
            params[8 * i + 5] = userInfo.getBirthDay().getTime() / 1000;
            params[8 * i + 6] = userInfo.getSchool();
            params[8 * i + 7] = userInfo.getHobby();
        }
        return params;
    }

    private String genMulFixSqlStr(int mulSize) {
        StringBuffer mulInserSql = new StringBuffer();
        mulInserSql.append("INSERT INTO `userInfo` (`userId`, `userName`, `gender`, `phone`, `address`, `birthDay`, `school`, `hobby`) VALUES (?,?,?,?,?,?,?,?)");
        for (int i = 1; i < mulSize; i++) {
            mulInserSql.append(",(?,?,?,?,?,?,?,?)");
        }
        return mulInserSql.toString();
    }

    private BlockingQueue<UserInfo> initUserInfoQueue(Integer size) {
        BlockingQueue<UserInfo> userInfoQueue = new LinkedBlockingQueue<>();
        for (int i = 0; i <= size; i++) {
            UserInfo userInfo = new UserInfo();
            userInfo.setAddress("shanghai" + i);
            userInfo.setBirthDay(new Date());
            userInfo.setGender(i % 2 == 0 ? "male" : "famale");
            userInfo.setHobby("programing,basketball,reading,driving");
            userInfo.setPhone("15700" + i);
            userInfo.setSchool("njCoolege");
            userInfo.setUserId(i);
            userInfo.setUserName("ping" + i);

            userInfoQueue.add(userInfo);
        }
        return userInfoQueue;
    }


    private class UserInfo {
        private Integer userId;
        private String userName;
        private String gender;
        private String phone;
        private String address;
        private Date birthDay;
        private String school;
        private String hobby;

        public Integer getUserId() {
            return userId;
        }

        public void setUserId(Integer userId) {
            this.userId = userId;
        }

        public String getUserName() {
            return userName;
        }

        public void setUserName(String userName) {
            this.userName = userName;
        }

        public String getGender() {
            return gender;
        }

        public void setGender(String gender) {
            this.gender = gender;
        }

        public String getPhone() {
            return phone;
        }

        public void setPhone(String phone) {
            this.phone = phone;
        }

        public String getAddress() {
            return address;
        }

        public void setAddress(String address) {
            this.address = address;
        }

        public Date getBirthDay() {
            return birthDay;
        }

        public void setBirthDay(Date birthDay) {
            this.birthDay = birthDay;
        }

        public String getSchool() {
            return school;
        }

        public void setSchool(String school) {
            this.school = school;
        }

        public String getHobby() {
            return hobby;
        }

        public void setHobby(String hobby) {
            this.hobby = hobby;
        }
    }


}

快速敲完后，立即试试插入1万条数据试试效率如何

分别访问：A.http://localhost:8080/mysql-test/mulTest?size=10000&sharding=true

B.http://localhost:8080/mysql-test/mulTest?size=10000&sharding=true

两个URL后发现之前的单条插入的sql语句插入10000条时间花费了9721多毫秒

而修改后的多条SQL插入10000条数据仅花费了373毫秒