WHCSRL 技术网

4分钟插入1000万条数据到mysql数据库表

准备工作

我用到的数据库为,mysql数据库8.0版本的,使用的InnoDB存储引

创建测试表

  1. CREATE TABLE `product` (
  2. `id` int NOT NULL AUTO_INCREMENT,
  3. `name` varchar(100) DEFAULT NULL COMMENT '商品名',
  4. `price` decimal(8,2) DEFAULT NULL COMMENT '价格',
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表';


一、使用java代码插入

1、编写程序

  1. public class InsertTest {
  2. public static void main(String[] args) throws ClassNotFoundException, SQLException {
  3. final String url = "jdbc:mysql://localhost:3306/blog?characterEncoding=UTF-8&serverTimezone=UTC";
  4. final String name = "com.mysql.cj.jdbc.Driver";
  5. final String user = "root";
  6. final String password = "root";
  7. Connection conn = null;
  8. Class.forName(name);//指定连接类型
  9. conn = DriverManager.getConnection(url, user, password);//获取连接
  10. if (conn!=null) {
  11. System.out.println("获取连接成功");
  12. insert(conn);
  13. }else {
  14. System.out.println("获取连接失败");
  15. }
  16. }
  17. public static void insert(Connection conn) {
  18. // 开始时间
  19. Long begin = System.currentTimeMillis();
  20. // sql前缀
  21. String prefix = "INSERT INTO product(`name`,`price`) VALUES";
  22. try {
  23. // 保存sql后缀
  24. StringBuffer suffix = new StringBuffer();
  25. // 设置事务为非自动提交
  26. conn.setAutoCommit(false);
  27. // 比起st,pst会更好些
  28. PreparedStatement pst = conn.prepareStatement(" ");//准备执行语句
  29. // 外层循环,总提交事务次数
  30. for (int i = 1; i <= 100; i++) {
  31. suffix = new StringBuffer();
  32. // 第j次提交步长
  33. for (int j = 1; j <= 100000; j++) {
  34. // 构建SQL后缀
  35. suffix.append("(");
  36. suffix.append("'"+"小米"+i*j+"',");
  37. suffix.append(""+ ran(100) +"");
  38. suffix.append("),");
  39. }
  40. // 构建完整SQL
  41. String sql = prefix + suffix.substring(0, suffix.length() - 1);
  42. // 添加执行SQL
  43. pst.addBatch(sql);
  44. // 执行操作
  45. pst.executeBatch();
  46. // 提交事务
  47. conn.commit();
  48. // 清空上一次添加的数据
  49. suffix = new StringBuffer();
  50. }
  51. // 头等连接
  52. pst.close();
  53. conn.close();
  54. } catch (SQLException e) {
  55. e.printStackTrace();
  56. }
  57. // 结束时间
  58. Long end = System.currentTimeMillis();
  59. // 耗时
  60. System.out.println("1000万条数据插入花费时间 : " + (end - begin) / 1000 + " s");
  61. System.out.println("插入完成");
  62. }
  63. //创建一个范围内的随机数
  64. public static int ran(int x)
  65. {
  66. java.util.Random random=new java.util.Random();
  67. // 返回0 to x的一个随机数但不会取到x,即返回[0,x)闭开区间的值。
  68. int rn=random.nextInt(x);
  69. return rn;
  70. }
  71. }

1000万条数据108完成,是不是感觉超级牛逼,
我每次插入10万条数据就提交一次事务,如果是一条一条差的话估计要好几个小时


2、总结

1、选择合适的存储引擎,这个会影响插入速度

2、索引不要创建太多,因为插入数据的时候会保存一份索引的数据

3、使用insert批量插入,每次插入10万条数据就提交一次事务,节省了大量时间

3、设置max_allowed_packet

 其实我在插入1000万条数据的时候遇到了一些问题,现在先来解决他们,一开始我插入100万条数据时候报错,控制台的信息如下:

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4232009 > 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

出现上面的错误是因为数据库表的 max_allowed_packet这个配置没配置足够大,因为默认的为4M的,后来我调为100M就没报错了

  1. set global max_allowed_packet = 100*1024*1024*
  2. 记住,设置好后重新登录数据库才能看的设置后的值
show VARIABLES like '%%%%max_allowed_packet%%%%'

二、使用存储过程插入

  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `blog`.`test_insert`()
  2. BEGIN
  3. -- 设置局部变量
  4. DECLARE product_name VARCHAR(20);
  5. DECLARE product_price int(20);
  6. DECLARE n int DEFAULT 1;
  7. start transaction;
  8. -- 开始执行循环体
  9. WHILE n<=10000000 do
  10. SET product_name = CONCAT('小米', n);
  11. SET product_price = FLOOR(RAND()*100);
  12. -- 开始插入数据
  13. INSERT INTO `product`(`name`,`price`) VALUES (product_name,product_price);
  14. SET n=n+1;
  15. END WHILE;
  16. COMMIT;
  17. END

在循环开始之前启动一次事务,循环结束后提交,这样每次 insert 就不会重新启动一个事务再提交了: 

下面我们优化一下,使用批量插入,拼接sql

  1. -- 调用存储过程,插入10000000条数据
  2. CALL test_insert2(10000000);
  1. -- 一个简单的存储过程,通过拼接sql批量向数据库插入数据
  2. -- row_num要插入数据的行数
  3. create procedure blog.test_insert2(in row_num int)
  4. begin
  5. declare name varchar(32);
  6. -- 计数器
  7. declare counter int default 0;
  8. -- 插入语句的前半部分
  9. set @pre_sql = "INSERT INTO `product`(`name`,`price`) VALUES ";
  10. set @exec_sql = @pre_sql;
  11. -- 循环语句
  12. repeat
  13. -- 循环拼接每一行数据
  14. set @exec_sql = concat(@exec_sql,
  15. "('" , concat('小米',FLOOR(RAND()*100)) , "'," , FLOOR(RAND()*100) , "),"
  16. );
  17. set counter=counter+1;
  18. -- 每拼接1000行数据或者技数器达到上限插入一次
  19. if counter mod 10000 = 0 then
  20. -- 出除sql最后一个逗号
  21. set @exec_sql = substring(@exec_sql, 1, char_length(@exec_sql)-1);
  22. -- 预处理需要执行的动态SQL,其中stmt是一个变量
  23. prepare stmt from @exec_sql;
  24. ## 执行SQL语句
  25. execute stmt;
  26. ## 释放掉预处理段
  27. deallocate prepare stmt;
  28. set @exec_sql = @pre_sql;
  29. end if;
  30. -- 直到计数器大于等于插入行数,退出循环
  31. until counter >= row_num
  32. end repeat;
  33. end

总共耗时7分钟

综合上面的测试数据,建议还是使用java代码进行批量插入比较快

推荐阅读