MySQL测试循环插入千万级数据

创建测试表

CREATE TABLE `xiaojia` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`uuid` VARCHAR (50) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 5999001 DEFAULT CHARSET = utf8;

创建一个循环插入的存储过程

CREATE PROCEDURE `xiaojia`( )
    BEGIN 
    DECLARE i INT DEFAULT 0;
    WHILE i < 10000 DO
        INSERT INTO xiaojia(uuid) VALUES(UUID());
        SET i = i + 1;
    END WHILE;
END;

调用刚刚创建的存储过程 call test_xiaojia(); 测试，发现一万条数据耗时 52 秒，如果是千万级数据，这个速度将无法忍受。😥

优化存储过程

优化的方法也是在网上找到的，用的是拼接批量插入的 sql 语句，可以发现速度确实提升很多😝

CREATE PROCEDURE `xiaojiaPlus`(IN sum INT)
    BEGIN
    DECLARE count INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    SET @exesql = concat("INSERT INTO xiaojia(uuid) VALUES");
    SET @exedata = "";
    SET count = 0;
    SET i = 0;
    WHILE count < sum DO
        SET @exedata = concat(@exedata, ",(UUID())");
        SET count = count + 1;
        SET i = i + 1;
        IF i % 1000 = 0 THEN
            SET @exedata = SUBSTRING( @exedata, 2 );
            SET @exesql = concat("INSERT INTO xiaojia(uuid) VALUES", @exedata);
            PREPARE stmt FROM @exesql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SET @exedata = "";
        END IF;
    END WHILE;
    IF length( @exedata ) > 0 THEN
        SET @exedata = SUBSTRING( @exedata, 2);
        SET @exesql = concat("INSERT INTO xiaojia(uuid) VALUES", @exedata);
        PREPARE stmt FROM @exesql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END;