4分鐘插入1000萬條資料到mysql資料庫表

準備作業

我用到的資料庫為，mysql資料庫8.0版本的，使用的InnoDB存盤引

創建測驗表

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

一、使用java代碼插入

1、撰寫程式

public class InsertTest {
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        final String url = "jdbc:mysql://localhost:3306/blog?characterEncoding=UTF-8&serverTimezone=UTC";
        final String name = "com.mysql.cj.jdbc.Driver";
        final String user = "root"; 
        final String password = "root";
        Connection conn = null;
        Class.forName(name);//指定連接型別 
        conn = DriverManager.getConnection(url, user, password);//獲取連接
        if (conn!=null) {
            System.out.println("獲取連接成功");
            insert(conn);
        }else {
            System.out.println("獲取連接失敗");
        }
 
    }
    public static void insert(Connection conn) {
        // 開始時間
        Long begin = System.currentTimeMillis();
        // sql前綴
        String prefix = "INSERT INTO product(`name`,`price`) VALUES";
        try {
            // 保存sql后綴
            StringBuffer suffix = new StringBuffer();
            // 設定事務為非自動提交
            conn.setAutoCommit(false);
            // 比起st，pst會更好些
            PreparedStatement pst = conn.prepareStatement(" ");//準備執行陳述句
            // 外層回圈，總提交事務次數
            for (int i = 1; i <= 100; i++) {
                suffix = new StringBuffer();
                // 第j次提交步長
                for (int j = 1; j <= 100000; j++) {
                    // 構建SQL后綴
                    suffix.append("(");
                    suffix.append("'"+"小米"+i*j+"',");
                    suffix.append(""+ ran(100) +"");
                    suffix.append("),");
                }
                // 構建完整SQL
                String sql = prefix + suffix.substring(0, suffix.length() - 1);
                // 添加執行SQL
                pst.addBatch(sql);
                // 執行操作
                pst.executeBatch();
                // 提交事務
                conn.commit();
                // 清空上一次添加的資料
                suffix = new StringBuffer();
            }
            // 頭等連接
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 結束時間
        Long end = System.currentTimeMillis();
        // 耗時
        System.out.println("1000萬條資料插入花費時間 : " + (end - begin) / 1000 + " s");
        System.out.println("插入完成");
    }


    //創建一個范圍內的亂數
    public static int ran(int x)
    {
        java.util.Random random=new java.util.Random();
        // 回傳0 to x的一個亂數但不會取到x，即回傳[0,x)閉開區間的值，
        int rn=random.nextInt(x);
        return rn;
    }
}

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就沒報錯了

set global max_allowed_packet = 100*1024*1024*
記住，設定好后重新登錄資料庫才能看的設定后的值show VARIABLES like '%max_allowed_packet%'

二、使用存盤程序插入

CREATE DEFINER=`root`@`localhost` PROCEDURE `blog`.`test_insert`()
BEGIN
	-- 設定區域變數
	DECLARE product_name VARCHAR(20);
	DECLARE product_price int(20);
	DECLARE n int DEFAULT 1;

	start transaction;
    -- 開始執行回圈體
    WHILE n<=10000000 do
		SET product_name = CONCAT('小米', n);
        SET product_price = FLOOR(RAND()*100);
       -- 開始插入資料
        INSERT INTO `product`(`name`,`price`) VALUES (product_name,product_price);
        SET n=n+1;
    END WHILE;
   	COMMIT;
END

在回圈開始之前啟動一次事務，回圈結束后提交，這樣每次 insert 就不會重新啟動一個事務再提交了：

下面我們優化一下，使用批量插入，拼接sql

-- 呼叫存盤程序，插入10000000條資料
CALL test_insert2(10000000);

-- 一個簡單的存盤程序，通過拼接sql批量向資料庫插入資料

-- row_num要插入資料的行數
create procedure blog.test_insert2(in row_num int)
begin 
	declare name varchar(32);
	-- 計數器
	declare counter int default 0;
	-- 插入陳述句的前半部分
	set @pre_sql = "INSERT INTO `product`(`name`,`price`) VALUES  ";
	set @exec_sql = @pre_sql;
	-- 回圈陳述句
	repeat
		-- 回圈拼接每一行資料
		set @exec_sql = concat(@exec_sql,
		  "('" , concat('小米',FLOOR(RAND()*100)) , "'," , FLOOR(RAND()*100) , "),"
		);

		set counter=counter+1;
		-- 每拼接1000行資料或者技數器達到上限插入一次
		if counter mod 10000 = 0 then
			-- 出除sql最后一個逗號
			set @exec_sql = substring(@exec_sql, 1, char_length(@exec_sql)-1);
			--  預處理需要執行的動態SQL，其中stmt是一個變數
			prepare stmt from @exec_sql;
		 	## 執行SQL陳述句
			execute stmt;
			## 釋放掉預處理段
			deallocate prepare stmt;
			set @exec_sql = @pre_sql;
		end if;
		-- 直到計數器大于等于插入行數，退出回圈
		until counter >= row_num
	end repeat;
end

總共耗時7分鐘

綜合上面的測驗資料，建議還是使用java代碼進行批量插入比較快