Mysql 通过存储过程构造10万条数据

Mysql 通过存储过程构造10万条数据

1. 创建表

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
 KEY `b` (`b`)
) ENGINE=InnoDB

2. 创建存储过程 并执行

-- 设置SQL语句的分隔符为 ;;
delimiter ;;
-- 创建一个名为 idata 的存储过程
create procedure idata()
begin
  -- 声明一个整数变量 i
  declare i int;
  -- 初始化变量 i 为 1
  set i=1;
  -- 开始一个循环，当 i 小于等于 100000 时继续循环
  while(i<=100000)do
    -- 向表 t 中插入一行数据，数据分别为 i, i, i
    insert into t values(i, i, i);
    -- 将变量 i 的值加 1
    set i=i+1;
  end while;
end;;
-- 恢复默认的SQL语句分隔符为 ;
delimiter ;
-- 调用存储过程 idata
call idata();

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

3.优化存储过程的效率

在MySQL中，存储过程本身并不直接支持多线程处理。MySQL的存储过程是单线程执行的，这意味着在一个存储过程中，所有的操作都是按顺序执行的，不能并行执行。

不过，你可以通过以下几种方式来提高插入操作的性能：

1. 批量插入：将多个插入操作合并为一个批量插入操作，减少与数据库的交互次数。
2. 使用事务：将多个插入操作放在一个事务中，可以提高性能。
3. 调整MySQL配置：通过调整MySQL的配置参数，如innodb_buffer_pool_size、innodb_log_file_size等，来提高插入性能。
4. 使用外部工具：使用外部编程语言（如Java）编写多线程程序，连接到MySQL数据库并执行插入操作。

4. 批量插入

以下是一个使用批量插入的示例：

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  start transaction;
  while(i<=100000)do
    insert into t values(i, i, i);
    if i % 1000 = 0 then
      commit;
      start transaction;
    end if;
    set i=i+1;
  end while;
  commit;
end;;
delimiter ;
call idata();

在这个示例中，每插入1000条记录就提交一次事务，这样可以减少事务的开销，提高插入性能。

5. 使用Java多线程插入

如果你希望使用多线程来提高插入性能，可以编写一个Java程序，使用多线程并发地插入数据。以下是一个简单的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

public class MultiThreadInsert {
    private static final int THREAD_COUNT = 10;
    private static final int TOTAL_RECORDS = 100000;
    private static final int RECORDS_PER_THREAD = TOTAL_RECORDS / THREAD_COUNT;

    public static void main(String[] args) throws Exception {
        ExecutorService executor = Executors.newFixedThreadPool(THREAD_COUNT);

        for (int i = 0; i < THREAD_COUNT; i++) {
            int start = i * RECORDS_PER_THREAD + 1;
            int end = (i + 1) * RECORDS_PER_THREAD;
            if (i == THREAD_COUNT - 1) {
                end = TOTAL_RECORDS;
            }
            executor.submit(new InsertTask(start, end));
        }

        executor.shutdown();
        executor.awaitTermination(1, TimeUnit.HOURS);
    }

    static class InsertTask implements Runnable {
        private final int start;
        private final int end;

        public InsertTask(int start, int end) {
            this.start = start;
            this.end = end;
        }

        @Override
        public void run() {
            try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/yourdb", "username", "password")) {
                String sql = "INSERT INTO t (col1, col2, col3) VALUES (?, ?, ?)";
                try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                    for (int i = start; i <= end; i++) {
                        pstmt.setInt(1, i);
                        pstmt.setInt(2, i);
                        pstmt.setInt(3, i);
                        pstmt.addBatch();
                        if (i % 1000 == 0) {
                            pstmt.executeBatch();
                        }
                    }
                    pstmt.executeBatch();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

在这个Java程序中，使用了多线程并发地插入数据，每个线程负责插入一部分数据。通过这种方式，可以显著提高插入性能。