互联网千万级别大数据量如何在本地模拟生成？采用MySQL存储过程轻松实现

文章导图

引言

在互联网大厂的业务场景中，常常需要处理千万级别甚至更多的数据量。然而，对于大多数普通公司而言，可能并没有机会接触到如此大规模的数据。但在开发和测试过程中，模拟这种大数据量的场景却非常有必要，比如性能测试、算法验证等。本文将详细介绍如何使用 MySQL 存储过程在本地模拟生成千万级别的大数据量。

为什么要模拟大数据量？有了这个大数据量能做什么？

在实际开发中，小数据量下运行良好的代码，在大数据量场景下可能会出现性能瓶颈、数据处理异常等问题。通过模拟大数据量，我们可以提前发现这些潜在问题，优化代码，确保系统在面对大规模数据时依然稳定高效。

那么，有了这个大数据量能做什么？

• 复杂查询分析：编写各种复杂的 SQL 查询语句，例如多表联合查询、嵌套查询、聚合查询等，测试数据库在大数据量下执行这些查询的性能表现，分析查询执行计划，找出可能存在的性能瓶颈，如索引使用不当、全表扫描等问题，并针对性地进行优化。

• 索引优化实验：通过为不同的列添加索引，观察索引对查询性能的影响。分析哪些索引能够有效提高查询速度，哪些索引可能因为数据分布等原因效果不明显甚至带来额外的开销，从而确定最优的索引策略。

• 分页查询与大数据分页优化 ：对大数据使用分页是非常常见的性能优化模型。分页通常有多种实现：

  • Offset 分页：LIMIT offset, size，适合小范围数据分页。
  • 基于游标的分页(CURSOR)：关联唯一键，保证效率更高。

• 此外还可以针对这个数据量进行分库分表，OOM 测试…

实战

以下数据库脚本及存储过程都可以直接在本地执行，已经过测试！

我们这里来创建三张表，分别为用户表、订单表和订单详情表。每个表仅需包含最基本的字段，具体要求如下：

1. 用户表（users)：包含一个主键，用于唯一标识每个用户。
2. 订单表(orders)：包含一个主键，同时有一个字段用于关联用户表的主键，但不使用外键进行强关联。
3. 订单详情表(order_details)：包含一个主键，以及一个字段用于关联订单表的主键，同样不使用外键进行强关联。

表结构：

-- 创建用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL, 
    user_email VARCHAR(255) NOT NULL, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, 
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    total_amount DECIMAL(10, 2) NOT NULL
);

-- 创建订单详情表
CREATE TABLE order_details (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL, 
    product_name VARCHAR(255) NOT NULL, 
    quantity INT NOT NULL, 
    price DECIMAL(10, 2) NOT NULL
);

存储过程用于生成数据：

然后，我们再创建一个存储过程来生成上述表的数据。生成的数据要具备关联关系，具体生成规则如下：

• 生成 200 万个用户记录到用户表中。
• 为每个用户随机生成 1 到 5 个订单记录，存储到订单表中。
• 为每个订单随机生成 1 到 5 个订单详情记录，存储到订单详情表中。

下面这是一个完整的存储过程实现，可批量生成200万用户、他们的订单以及订单详情数据：

1. 三层嵌套循环架构
   • 外层循环：生成200万用户（user_id从1到2,000,000）
   • 中层循环：每个用户生成1-5个订单（随机数量）
   • 内层循环：每个订单生成1-5个订单详情（随机数量）
2. 数据关联性保障
   • 用户表插入后立即获取自增ID
   • 订单表使用当前用户ID作为逻辑外键
   • 订单详情表使用当前订单ID作为逻辑外键

DELIMITER $$

-- 创建生成数据的存储过程
CREATE PROCEDURE GenerateTestData()
BEGIN
    DECLARE user_counter INT DEFAULT 1;
    DECLARE order_counter INT DEFAULT 1;
    DECLARE order_detail_counter INT DEFAULT 1;

    -- 临时变量
    DECLARE total_users INT DEFAULT 2000000;  -- 总用户数
    DECLARE rand_order_count INT;  -- 每个用户的订单数
    DECLARE rand_order_detail_count INT;  -- 每笔订单的详情数
    DECLARE product_names TEXT;  -- 商品名称池，供随机选择

    -- 模拟商品名集合
    SET product_names = "商品A,商品B,商品C,商品D,商品E";

    -- 循环生成用户
    WHILE user_counter <= total_users DO

        -- 插入用户
        INSERT INTO users (user_name, user_email, created_at) 
        VALUES (CONCAT('User', user_counter), CONCAT('user', user_counter, '@test.com'), NOW());

        -- 随机生成每个用户的订单数（1-5）
        SET rand_order_count = FLOOR(1 + RAND() * 5);

        WHILE rand_order_count > 0 DO
            -- 为该用户插入订单记录
            INSERT INTO orders (user_id, order_date, total_amount)
            VALUES (user_counter, NOW(), ROUND(RAND() * 1000, 2));

            -- 获取当前订单ID
            SET order_counter = LAST_INSERT_ID();

            -- 随机生成订单详情数（1-5）
            SET rand_order_detail_count = FLOOR(1 + RAND() * 5);

            WHILE rand_order_detail_count > 0 DO
                -- 随机选择商品名及数量
                INSERT INTO order_details (order_id, product_name, quantity, price)
                VALUES (
                    order_counter,
                    SUBSTRING_INDEX(SUBSTRING_INDEX(product_names, ',', FLOOR(1 + RAND() * 5)), ',', -1),
                    FLOOR(1 + RAND() * 10),
                    ROUND(RAND() * 500, 2)
                );

                SET rand_order_detail_count = rand_order_detail_count - 1;
            END WHILE;

            SET rand_order_count = rand_order_count - 1;
        END WHILE;

        SET user_counter = user_counter + 1;
    END WHILE;
END$$

DELIMITER ;

如何执行存储过程：

在创建表、存储过程并编译通过后，你可以通过以下操作生成测试数据：

CALL GenerateTestData();

各表的数据生成逻辑总结：

1. 用户表 (users)
   • 包含了200万个用户，每个用户有一个独一无二的user_id。
   • 数据字段包括user_name和user_email，统一随机。
2. 订单表 (orders)
   • 每个用户随机生成1-5个订单，订单与用户通过user_id关联。
   • 每笔订单随机生成total_amount表示订单总金额。
3. 订单详情表 (order_details)
   • 每笔订单有1-5条随机的订单详情，包括商品名称、数量和价格。
   • 数据提供关联测试。

数据生成规模：

假设：

• 用户200万
• 每用户1-5个订单（均值3）
• 每个订单1-5个详情（均值3）

预计生成：

1. 用户表：200万条
2. 订单表：约 600万 条
3. 订单详情表：约 1800万 条

实际生成：

我们这里花了7个多小时，不过我这里没采用批量提交事务优化

[2025-03-21 21:59:11] 1 row affected in 7 h 47 m 2 s 13 ms

我们可以看到和我们预估的基本差不多，这也说明了在数量足够的情况下，预估理论上基本上会接近于实际值！

性能优化建议：

• 批量提交：每生成几千条记录提交一次事务
• 索引延迟创建：数据生成完成后统一创建索引

ALTER TABLE orders ADD INDEX (user_id);
ALTER TABLE order_details ADD INDEX (order_id);

优化版本存储过程

我这里9000条再提交一次事务，执行完那叫一个快！

DELIMITER $$

CREATE PROCEDURE GenerateTestData()
BEGIN
    DECLARE user_counter INT DEFAULT 1;
    DECLARE order_counter INT DEFAULT 1;
    DECLARE total_users INT DEFAULT 2000000;
    DECLARE rand_order_count INT;
    DECLARE rand_order_detail_count INT;
    DECLARE product_names TEXT DEFAULT '商品A,商品B,商品C,商品D,商品E,商品F,商品G,商品H,商品I,商品J,商品K,商品L,商品M,商品N,商品O,商品P,商品Q,商品R,商品S,商品T,商品U,商品V,商品W,商品X,商品Y,商品Z';
    DECLARE commit_counter INT DEFAULT 0;

    -- 时间范围时间戳（2015-2025）
    DECLARE start_ts INT DEFAULT UNIX_TIMESTAMP('2015-01-01');
    DECLARE end_ts INT DEFAULT UNIX_TIMESTAMP('2025-12-31');
    DECLARE ts_range INT DEFAULT end_ts - start_ts;

    SET autocommit = 0;
    START TRANSACTION;

    WHILE user_counter <= total_users DO
            -- 插入用户（随机创建时间）
            INSERT INTO users (user_name, user_email, created_at)
            VALUES (
                       CONCAT('User', user_counter),
                       CONCAT('user', user_counter, '@test.com'),
                       FROM_UNIXTIME(start_ts + FLOOR(RAND() * ts_range))
                   );

            SET commit_counter = commit_counter + 1;

            -- 随机生成订单数量（1-5）
            SET rand_order_count = FLOOR(1 + RAND() * 5);
            WHILE rand_order_count > 0 DO
                    -- 插入订单（随机时间/金额）
                    INSERT INTO orders (user_id, order_date, total_amount)
                    VALUES (
                               user_counter,
                               FROM_UNIXTIME(start_ts + FLOOR(RAND() * ts_range)),
                               ROUND(1 + RAND() * 999999, 2)  -- 1-100万随机金额
                           );

                    SET order_counter = LAST_INSERT_ID();
                    SET commit_counter = commit_counter + 1;

                    -- 随机生成订单详情数量（1-5）
                    SET rand_order_detail_count = FLOOR(1 + RAND() * 5);
                    WHILE rand_order_detail_count > 0 DO
                            -- 插入订单详情（数量1-100万，价格1-100万）
                            INSERT INTO order_details (order_id, product_name, quantity, price)
                            VALUES (
                                       order_counter,
                                       SUBSTRING_INDEX(SUBSTRING_INDEX(product_names, ',', FLOOR(1 + RAND() * 25)), ',', -1),
                                       FLOOR(1 + RAND() * 999999),  -- 1-100万随机数量
                                       ROUND(1 + RAND() * 999999, 2)  -- 1-100万随机价格
                                   );

                            SET commit_counter = commit_counter + 1;
                            IF commit_counter >= 9000 THEN
                                COMMIT;
                                SET commit_counter = 0;
                                START TRANSACTION;
                            END IF;

                            SET rand_order_detail_count = rand_order_detail_count - 1;
                        END WHILE;

                    SET rand_order_count = rand_order_count - 1;
                END WHILE;

            SET user_counter = user_counter + 1;
        END WHILE;

    COMMIT;
    SET autocommit = 1;
END$$

DELIMITER ;

重新执行测试结果如下：

[2025-03-25 22:39:12] 1 row affected in 4 m 42 s 663 ms
原来每一行就提交一次，需要7个多小时，现在优化成9000条一次，只要4分钟，数据量大的情况下，这种批量提交的优化效率简直是史诗级别的！大家也可以自己在本地体验一下！

TODO：

后续我会在这个大数据量的基础上分析，MySQL的深度分页方案、索引优化方案。。。