高效数据库中插入百万数据（耗时几秒）

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

在建立表的时候不建立索引，插入百万数据效率高一些

适用于MySQL8亲测

-- =============================================
-- 1. 创建测试表（若已存在则先删除）
-- =============================================
DROP TABLE IF EXISTS test_index_1;
CREATE TABLE test_index_1 (
    id INT AUTO_INCREMENT COMMENT '主键',
    a INT NOT NULL COMMENT '联合索引第一个字段',
    b INT NOT NULL COMMENT '联合索引第二个字段',
    c INT NOT NULL COMMENT '联合索引第三个字段',
    d INT NOT NULL COMMENT '',
    PRIMARY KEY (id),
    INDEX idx_a_b_c (a, b, c) COMMENT '联合索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'test_index_1表';

-- =============================================
-- 2. 创建批量插入百万数据的存储过程（完整无省略）
-- =============================================
DROP PROCEDURE IF EXISTS insert_test_index_1;
DELIMITER //  -- 临时修改语句结束符为//，避免存储过程内的;提前终止

CREATE PROCEDURE insert_test_index_1()
BEGIN
    -- 定义变量：循环计数器、批量插入行数、总目标行数
    DECLARE i INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 1000;  -- 每次批量插入1000行，平衡效率与内存
    DECLARE total_rows INT DEFAULT 1000000;  -- 总插入100万行
    
    -- ========== 核心优化：关闭自动提交，减少事务开销 ==========
    SET autocommit = 0;
    -- 临时禁用索引更新（插入后重建，大幅提升速度）
    ALTER TABLE test_index_1 DISABLE KEYS;

    -- ========== 循环批量插入数据 ==========
    WHILE i <= total_rows DO
        INSERT INTO test_index_1 (a, b, c, d)
        SELECT 
            -- a字段：基础值(i+num)*1000 + 随机数，保证高区分度
            (i + t.num) * 1000 + FLOOR(RAND() * 1000),
            -- b字段：基础值(i+num)*100 + 随机数，保证高区分度
            (i + t.num) * 100 + FLOOR(RAND() * 100),
            -- c字段：基础值(i+num) + 随机数，保证高区分度
            (i + t.num) + FLOOR(RAND() * 10000),
            -- d字段：纯随机数，扩大取值范围
            FLOOR(RAND() * 100000000)
        FROM (
            -- 递归CTE生成0~999的数字序列（MySQL 8.0+支持），用于批量生成1000行数据
            WITH RECURSIVE nums AS (
                SELECT 0 AS num  -- 初始值
                UNION ALL
                SELECT num + 1 FROM nums WHERE num < 999  -- 递归生成到999
            )
            SELECT num FROM nums
        ) AS t
        WHERE (i + t.num) <= total_rows;  -- 避免最后一批超出百万行

        -- 每批量插入1000行提交一次，避免事务过大
        COMMIT;
        -- 计数器递增
        SET i = i + batch_size;
    END WHILE;

    -- ========== 恢复配置 ==========
    ALTER TABLE test_index_1 ENABLE KEYS;  -- 重建索引
    SET autocommit = 1;  -- 恢复自动提交

    -- 输出完成提示（MySQL命令行可见）
    SELECT '百万数据插入完成！' AS result;
END //

DELIMITER ;  -- 恢复语句结束符为;

-- =============================================
-- 3. 执行存储过程（开始插入百万数据）
-- =============================================
CALL insert_test_index_1();

-- =============================================
-- 4. 验证插入结果（可选）
-- =============================================
-- 统计总数据量
SELECT COUNT(*) AS total_rows FROM test_index_1;
-- 验证a字段区分度（无重复则返回空）
SELECT a, COUNT(*) AS repeat_count FROM test_index_1 GROUP BY a HAVING repeat_count > 1 LIMIT 10;
-- 查看字段取值范围（确认区分度）
SELECT 
    MIN(a) AS min_a, MAX(a) AS max_a,
    MIN(b) AS min_b, MAX(b) AS max_b,
    MIN(c) AS min_c, MAX(c) AS max_c
FROM test_index_1;

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花