高效数据库中插入百万数据（耗时几秒）
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：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花
